sqlserver 自定义聚合函数后调用报数据类型转换错误

消息 8114，级别 16，状态 5，第 1 行
从数据类型 varchar 转换为 float 时出错。

select ci.*,gei.company_code,gei.examine_id,gei.risk_judge,ttc5.work_shop_name,ttc6.over_work_shop_name
from company_info ci 
left join gs_examine_info gei on ci.company_code=gei.company_code and gei.project_id='2' 
left join (select gwi.examine_id,dbo.Concatenate(distinct gwi.work_shop_name+',') as work_shop_name
	from gs_workspace_info gwi
	GROUP BY gwi.examine_id
	) ttc5 on ttc5.examine_id=gei.examine_id 
left join ( select gwi3.examine_id,dbo.Concatenate(distinct work_shop_name+'|'+harm_name+'|'+ghi.over_number+',') as over_work_shop_name

	from gs_workspace_info gwi3 
	inner join gs_harm_info ghi on gwi3.workspace_id=ghi.workspace_id 
	inner join profession_harm_base_data phbd on phbd.harm_code=ghi.profession_harm_code 
	group by gwi3.examine_id
	) ttc6 on ttc6.examine_id=gei.examine_id 
 where (( ci.position_district_id = 4 ) and ( ci.status = 1 or ci.status = 2 ) and ( ci.status = 1 or ci.status = 2 )) 
 order by ci.company_id 

using System;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls = true, IsInvariantToDuplicates = false, IsInvariantToOrder = false, MaxByteSize = 8000, Name = "Concatenate")]
public class Concatenate : IBinarySerialize
{
    private StringBuilder _intermediateResult;

    internal string IntermediateResult {
        get
        {
            return _intermediateResult.ToString();
        } 
    }

    public void Init()
    {
        _intermediateResult = new StringBuilder();
    }

    public void Accumulate(SqlString value)
    {
        if (value.IsNull) return;
        _intermediateResult.Append(value.Value);
    }

    public void Merge(Concatenate other)
    {
        if (null == other)
            return;

        _intermediateResult.Append(other._intermediateResult);
    }

    public SqlString Terminate()
    {
        var output = string.Empty;

        if (_intermediateResult != null && _intermediateResult.Length > 0)
            output = _intermediateResult.ToString(0, _intermediateResult.Length - 1);

        return new SqlString(output);
    }

    public void Read(BinaryReader reader)
    {
        if (reader == null) 
            throw new ArgumentNullException("reader");
        
        _intermediateResult = new StringBuilder(reader.ReadString());
    }

    public void Write(BinaryWriter writer)
    {
        if (writer == null) 
            throw new ArgumentNullException("writer");
        
        writer.Write(_intermediateResult.ToString());
    }
}

14 个解决方案

#1

distinct work_shop_name+'|'+harm_name+'|'+ghi.over_number+','

中的work_shop_name和harm_name都是varchar类型，over_number是float类型

求指点该如何修改啊

#2

把这个语句涉及到的表结构都说一下

原因：存在了隐性转换

#3

dbo.Concatenate(distinct work_shop_name+'|'+harm_name+'|'+ghi.over_number+',') as over_work_shop_name

应该是这句，改成这样的

dbo.Concatenate(distinct work_shop_name+'|'+harm_name+'|'+ltrim(ghi.over_number)+',') as over_work_shop_name

#4

引用 3 楼 wmxcn2000 的回复:

dbo.Concatenate(distinct work_shop_name+'|'+harm_name+'|'+ghi.over_number+',') as over_work_shop_name

应该是这句，改成这样的

dbo.Concatenate(distinct work_shop_name+'|'+harm_name+'|'+ltrim(ghi.over_number)+',') as over_work_shop_name

不对啊，报错是float转varchar错误啊，感觉可能是函数定义错了

CREATE AGGREGATE [dbo].[Concatenate] (@value [nvarchar](max))
RETURNS [nvarchar](max)
EXTERNAL NAME [concat].[Concatenate]
GO

returns 是nvarchar类型 ，应该怎么改好呢

#5

引用 3 楼 wmxcn2000 的回复:

dbo.Concatenate(distinct work_shop_name+'|'+harm_name+'|'+ghi.over_number+',') as over_work_shop_name

应该是这句，改成这样的

dbo.Concatenate(distinct work_shop_name+'|'+harm_name+'|'+ltrim(ghi.over_number)+',') as over_work_shop_name

我一开始表述错了 是float转varchar错误

#6

好吧 被绕晕了 确实是varchar转float错误啊 float转varchar也不会报错啊

#7

引用 6 楼 tiantianxihongshi 的回复:

好吧 被绕晕了 确实是varchar转float错误啊 float转varchar也不会报错啊

按 3# 的建议修改了吗？

#8

引用 7 楼 wmxcn2000 的回复:

Quote: 引用 6 楼 tiantianxihongshi 的回复:

好吧 被绕晕了 确实是varchar转float错误啊 float转varchar也不会报错啊

按 3# 的建议修改了吗？

改了的 没用

#9

把涉及到的到表结果都说一下，估计还有别的字段是数值型；

#10

引用 8 楼 tiantianxihongshi 的回复:

Quote: 引用 7 楼 wmxcn2000 的回复:

Quote: 引用 6 楼 tiantianxihongshi 的回复:

好吧 被绕晕了 确实是varchar转float错误啊 float转varchar也不会报错啊

按 3# 的建议修改了吗？

改了的 没用

改了以后是这样的结果，有些数据丢失了

#11

好了 解决了

#12

select gwi3.examine_id,dbo.Concatenate(distinct work_shop_name+'|'+harm_name+'|'+isnull(ltrim(ghi.over_number),0)+',') as merge_str
from gs_workspace_info gwi3 
inner join gs_harm_info ghi on gwi3.workspace_id=ghi.workspace_id 
inner join profession_harm_base_data phbd on phbd.harm_code=ghi.profession_harm_code 
group by gwi3.examine_id

贴上说明

#13

好的，解决了就好；

#14

引用 13 楼 wmxcn2000 的回复:

好的，解决了就好；

引用 13 楼 wmxcn2000 的回复:

好的，解决了就好；

嗯嗯，就是感觉很奇怪，为什么下面那个能输出，上面那个sql就必须要加ltrim才行呢