在将 varchar 值 '配方' 转换成数据类型 int 时失败。只执行一段的时候不会出错,但是整段运行出现错误

qq_32320747 2018-05-16 10:47:56
SELECT '配方' AS ExitStatus,  COUNT(DISTINCT Charge.ID) AS Pages, COUNT(DISTINCT List.ID) AS Num, 
SendUser.user_name AS UserID, Charge.sDeptCode + '-' + MedType.Name AS MedTypeName
FROM In_wsz_ChargeSum Charge
INNER JOIN In_wsz_ChargeMedList List ON Charge.ID = List.SumChargeID
INNER JOIN public_user_list SendUser ON Charge.SendUserID = SendUser.user_name
INNER JOIN In_wsz_MedType MedType ON Charge.MedType = MedType.MedType
WHERE (Charge.SendStatus = 1) AND (Charge.MedType = 11 OR Charge.MedType = 12
OR Charge.MedType = 13 OR Charge.MedType = 14) AND (Charge.SendTime > '2018-05-16 00:00:00')
AND (Charge.SendTime < '2018-05-16 23:59:59')
GROUP BY Charge.SendUserID, SendUser.user_name, MedType.Name, Charge.sDeptCode
UNION ALL
SELECT '电子处方' AS MedTypeName, '配方' AS ExitStatus, COUNT(DISTINCT Charge.ID) AS Pages,
COUNT(DISTINCT List.ID) AS Num, SendUser.user_name AS UserID FROM In_wsz_ChargeSum Charge
INNER JOIN In_wsz_ChargeMedList List ON Charge.ID = List.SumChargeID
INNER JOIN public_user_list SendUser ON Charge.SendUserID = SendUser.user_name
INNER JOIN In_wsz_MedType MedType ON Charge.MedType = MedType.MedType
WHERE (Charge.SendStatus = 1) AND (Charge.MedType <> 24 AND Charge.MedType <> 34
AND Charge.MedType <> 11 AND Charge.MedType <> 12 AND Charge.MedType <> 13 AND Charge.MedType <> 14
AND Charge.MedType<>82) AND (Charge.SendTime > '2018-05-16 00:00:00')
AND (Charge.SendTime < '2018-05-16 23:59:59')
GROUP BY Charge.SendUserID, SendUser.user_name
UNION ALL
SELECT '配方' AS ExitStatus, COUNT(DISTINCT Charge.ID) AS Pages, COUNT(DISTINCT List.ID) AS Num,
SendUser.user_name AS UserID, Charge.sDeptCode + '-' + MedType.Name AS MedTypeName
FROM In_wsz_ChargeSum Charge INNER JOIN In_wsz_ChargeMedList List ON Charge.ID = List.SumChargeID
INNER JOIN public_user_list SendUser ON Charge.SendUserID = SendUser.user_name
INNER JOIN In_wsz_MedType MedType ON Charge.MedType = MedType.MedType
WHERE (Charge.SendStatus = 1) AND (Charge.MedType = 24 OR Charge.MedType = 34)
AND (Charge.SendTime > '2018-05-16 00:00:00') AND (Charge.SendTime < '2018-05-16 23:59:59')
GROUP BY Charge.SendUserID, SendUser.user_name, MedType.Name, Charge.sDeptCode
UNION ALL
SELECT '退方' AS MedTypeName,COUNT(DISTINCT Charge.ID) AS Pages, COUNT(DISTINCT List.ID) AS Num,
'退方' AS ExitStatus, SendUser.user_name AS UserID FROM In_wsz_ChargeSum Charge
INNER JOIN In_wsz_ChargeMedList List ON Charge.ID = List.SumChargeID
INNER JOIN public_user_list SendUser ON Charge.SendUserID = SendUser.user_name
WHERE (Charge.MedType =82) AND (Charge.SendStatus = 1)
AND (Charge.SendTime > '2018-05-16 00:00:00') AND (Charge.SendTime < '2018-05-16 23:59:59')
GROUP BY Charge.SendUserID, SendUser.user_name
...全文
1302 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
zyy1111111 2018-06-04
  • 打赏
  • 举报
回复
首先看顺序是否对应,再在数据库中寻找字符是否正确对应
shoppo0505 2018-05-17
  • 打赏
  • 举报
回复
应该是在这里吧: Charge.sDeptCode + '-' + MedType.Name AS MedTypeName 可能Charge.sDeptCode是int类型的,你需要先转成varchar 再连接
qq_32320747 2018-05-16
  • 打赏
  • 举报
回复
引用 5 楼 shinger126 的回复:
[quote=引用 1 楼 qq_32320747 的回复:]
SELECT '电子处方' AS MedTypeName, '.' AS ExitStatus, COUNT(DISTINCT Charge.ID) AS Pages, 
  COUNT(DISTINCT List.ID) AS Num, SendUser.user_name AS UserID  FROM In_wsz_ChargeSum Charge 
  INNER JOIN In_wsz_ChargeMedList List ON Charge.ID = List.SumChargeID 
  INNER JOIN public_user_list SendUser ON Charge.SendUserID = SendUser.user_name
   INNER JOIN In_wsz_MedType MedType ON Charge.MedType = MedType.MedType 
   WHERE (Charge.SendStatus = 1) AND (Charge.MedType <> 24 AND Charge.MedType <> 34 
   AND Charge.MedType <> 11 AND Charge.MedType <> 12 AND Charge.MedType <> 13 AND Charge.MedType <> 14 
   AND Charge.MedType<>82) AND (Charge.SendTime > '2018-05-16 00:00:00') 
   AND (Charge.SendTime < '2018-05-16 23:59:59') 
   GROUP BY Charge.SendUserID, SendUser.user_name 
我找到在第二段出现错误,为什么出错啊?找不到原因
SELECT '配方' AS ExitStatus, COUNT(DISTINCT Charge.ID) AS Pages 第一个查询,第二个列是整数型的,因为是执行的count运算,union的第二个查询,第二个列是SELECT '电子处方' AS MedTypeName, '配方' AS ExitStatus 字符串'配方',sql会将对应的第二个列转换成第一个查询的第二个列的数据类型,也就是int型,所以就会报错将varchar值'配方'转换成int型失败了[/quote] 不是不是,我调换了顺序,你看#3,但是还是报错
shinger126 2018-05-16
  • 打赏
  • 举报
回复
引用 1 楼 qq_32320747 的回复:
SELECT '电子处方' AS MedTypeName, '.' AS ExitStatus, COUNT(DISTINCT Charge.ID) AS Pages, 
  COUNT(DISTINCT List.ID) AS Num, SendUser.user_name AS UserID  FROM In_wsz_ChargeSum Charge 
  INNER JOIN In_wsz_ChargeMedList List ON Charge.ID = List.SumChargeID 
  INNER JOIN public_user_list SendUser ON Charge.SendUserID = SendUser.user_name
   INNER JOIN In_wsz_MedType MedType ON Charge.MedType = MedType.MedType 
   WHERE (Charge.SendStatus = 1) AND (Charge.MedType <> 24 AND Charge.MedType <> 34 
   AND Charge.MedType <> 11 AND Charge.MedType <> 12 AND Charge.MedType <> 13 AND Charge.MedType <> 14 
   AND Charge.MedType<>82) AND (Charge.SendTime > '2018-05-16 00:00:00') 
   AND (Charge.SendTime < '2018-05-16 23:59:59') 
   GROUP BY Charge.SendUserID, SendUser.user_name 
我找到在第二段出现错误,为什么出错啊?找不到原因
SELECT '配方' AS ExitStatus, COUNT(DISTINCT Charge.ID) AS Pages 第一个查询,第二个列是整数型的,因为是执行的count运算,union的第二个查询,第二个列是SELECT '电子处方' AS MedTypeName, '配方' AS ExitStatus 字符串'配方',sql会将对应的第二个列转换成第一个查询的第二个列的数据类型,也就是int型,所以就会报错将varchar值'配方'转换成int型失败了
qq_32320747 2018-05-16
  • 打赏
  • 举报
回复
引用 2 楼 shinger126 的回复:
你UNION的各个查询,列不一致
我调换了顺序,出现varchar 值 '电子处方' 转换成数据类型 int 时失败。。。你看3#,为什么啊?
qq_32320747 2018-05-16
  • 打赏
  • 举报
回复
SELECT   COUNT(DISTINCT Charge.ID) AS Pages, COUNT(DISTINCT List.ID) AS Num, 
SendUser.user_name AS UserID, Charge.sDeptCode + '-' + MedType.Name AS MedTypeName, '配方' AS ExitStatus
FROM In_wsz_ChargeSum Charge 
INNER JOIN In_wsz_ChargeMedList List ON Charge.ID = List.SumChargeID 
INNER JOIN public_user_list SendUser ON Charge.SendUserID = SendUser.user_name 
INNER JOIN In_wsz_MedType MedType ON Charge.MedType = MedType.MedType 
WHERE (Charge.SendStatus = 1) AND (Charge.MedType = 11 OR Charge.MedType = 12 
OR Charge.MedType = 13 OR Charge.MedType = 14) AND (Charge.SendTime > '2018-05-16 00:00:00') 
AND (Charge.SendTime < '2018-05-16 23:59:59')  
GROUP BY Charge.SendUserID, SendUser.user_name, MedType.Name, Charge.sDeptCode 
UNION ALL
  SELECT   COUNT(DISTINCT Charge.ID) AS Pages,  COUNT(DISTINCT List.ID) AS Num, 
  SendUser.user_name AS UserID ,'电子处方' AS MedTypeName,'配方' AS ExitStatus 
   FROM In_wsz_ChargeSum Charge 
  INNER JOIN In_wsz_ChargeMedList List ON Charge.ID = List.SumChargeID 
  INNER JOIN public_user_list SendUser ON Charge.SendUserID = SendUser.user_name
   INNER JOIN In_wsz_MedType MedType ON Charge.MedType = MedType.MedType 
   WHERE (Charge.SendStatus = 1) AND (Charge.MedType <> 24 AND Charge.MedType <> 34 
   AND Charge.MedType <> 11 AND Charge.MedType <> 12 AND Charge.MedType <> 13 AND Charge.MedType <> 14 
   AND Charge.MedType<>82) AND (Charge.SendTime > '2018-05-16 00:00:00') 
   AND (Charge.SendTime < '2018-05-16 23:59:59') 
   GROUP BY Charge.SendUserID, SendUser.user_name 
   UNION ALL 
   SELECT   COUNT(DISTINCT Charge.ID) AS Pages, COUNT(DISTINCT List.ID) AS Num,
   SendUser.user_name AS UserID, Charge.sDeptCode + '-' + MedType.Name AS MedTypeName,'配方' AS ExitStatus 
   FROM In_wsz_ChargeSum Charge INNER JOIN In_wsz_ChargeMedList List ON Charge.ID = List.SumChargeID 
   INNER JOIN public_user_list SendUser ON Charge.SendUserID = SendUser.user_name
    INNER JOIN In_wsz_MedType MedType ON Charge.MedType = MedType.MedType
     WHERE (Charge.SendStatus = 1) AND (Charge.MedType = 24 OR Charge.MedType = 34) 
     AND (Charge.SendTime > '2018-05-16 00:00:00') AND (Charge.SendTime < '2018-05-16 23:59:59')  
     GROUP BY Charge.SendUserID, SendUser.user_name, MedType.Name, Charge.sDeptCode
      UNION ALL  
      SELECT  COUNT(DISTINCT Charge.ID) AS Pages, COUNT(DISTINCT List.ID) AS Num,SendUser.user_name AS UserID,
       '退方' AS MedTypeName,'' AS ExitStatus  FROM In_wsz_ChargeSum Charge
        INNER JOIN In_wsz_ChargeMedList List ON Charge.ID = List.SumChargeID 
        INNER JOIN public_user_list SendUser ON Charge.SendUserID = SendUser.user_name
         WHERE (Charge.MedType =82) AND (Charge.SendStatus = 1)
          AND (Charge.SendTime > '2018-05-16 00:00:00') AND (Charge.SendTime < '2018-05-16 23:59:59')
           GROUP BY Charge.SendUserID, SendUser.user_name
好吧,好像不是,我不知道为啥,我整理了顺序,然后 出现在将 varchar 值 '电子处方' 转换成数据类型 int 时失败。为什么啊?
shinger126 2018-05-16
  • 打赏
  • 举报
回复
你UNION的各个查询,列不一致
qq_32320747 2018-05-16
  • 打赏
  • 举报
回复
SELECT '电子处方' AS MedTypeName, '.' AS ExitStatus, COUNT(DISTINCT Charge.ID) AS Pages, 
  COUNT(DISTINCT List.ID) AS Num, SendUser.user_name AS UserID  FROM In_wsz_ChargeSum Charge 
  INNER JOIN In_wsz_ChargeMedList List ON Charge.ID = List.SumChargeID 
  INNER JOIN public_user_list SendUser ON Charge.SendUserID = SendUser.user_name
   INNER JOIN In_wsz_MedType MedType ON Charge.MedType = MedType.MedType 
   WHERE (Charge.SendStatus = 1) AND (Charge.MedType <> 24 AND Charge.MedType <> 34 
   AND Charge.MedType <> 11 AND Charge.MedType <> 12 AND Charge.MedType <> 13 AND Charge.MedType <> 14 
   AND Charge.MedType<>82) AND (Charge.SendTime > '2018-05-16 00:00:00') 
   AND (Charge.SendTime < '2018-05-16 23:59:59') 
   GROUP BY Charge.SendUserID, SendUser.user_name 
我找到在第二段出现错误,为什么出错啊?找不到原因
zbdzjx 2018-05-16
  • 打赏
  • 举报
回复
引用 11 楼 qq_32320747 的回复:
[quote=引用 9 楼 zbdzjx 的回复:] 一列一列的试,每次只select一列,其他列不要,看最终是哪列出的问题。 看SQL语句,应该是MedTypeName这一列。 不要union all,几个SQL语句一起执行,看这一列的结果,看看是否有可能是系统对某些SQL语句的结果进行转换了。
是MedTypeName出错了。那怎么把MedTypeName转换为varchar?[/quote] 实在不行,就每段SQL中,在MedTypeName外面套上一层CONVERT,看看还报不报错。
qq_32320747 2018-05-16
  • 打赏
  • 举报
回复
引用 9 楼 zbdzjx 的回复:
一列一列的试,每次只select一列,其他列不要,看最终是哪列出的问题。 看SQL语句,应该是MedTypeName这一列。 不要union all,几个SQL语句一起执行,看这一列的结果,看看是否有可能是系统对某些SQL语句的结果进行转换了。
是MedTypeName出错了。那怎么把MedTypeName转换为varchar?
qq_32320747 2018-05-16
  • 打赏
  • 举报
回复
引用 9 楼 zbdzjx 的回复:
一列一列的试,每次只select一列,其他列不要,看最终是哪列出的问题。 看SQL语句,应该是MedTypeName这一列。 不要union all,几个SQL语句一起执行,看这一列的结果,看看是否有可能是系统对某些SQL语句的结果进行转换了。
我一列一列的实验都没有问题,但是union all之后就出错
zbdzjx 2018-05-16
  • 打赏
  • 举报
回复
一列一列的试,每次只select一列,其他列不要,看最终是哪列出的问题。 看SQL语句,应该是MedTypeName这一列。 不要union all,几个SQL语句一起执行,看这一列的结果,看看是否有可能是系统对某些SQL语句的结果进行转换了。
qq_32320747 2018-05-16
  • 打赏
  • 举报
回复
是一样的
shinger126 2018-05-16
  • 打赏
  • 举报
回复
引用 6 楼 qq_32320747 的回复:
[quote=引用 5 楼 shinger126 的回复:] [quote=引用 1 楼 qq_32320747 的回复:]
SELECT '电子处方' AS MedTypeName, '.' AS ExitStatus, COUNT(DISTINCT Charge.ID) AS Pages, 
  COUNT(DISTINCT List.ID) AS Num, SendUser.user_name AS UserID  FROM In_wsz_ChargeSum Charge 
  INNER JOIN In_wsz_ChargeMedList List ON Charge.ID = List.SumChargeID 
  INNER JOIN public_user_list SendUser ON Charge.SendUserID = SendUser.user_name
   INNER JOIN In_wsz_MedType MedType ON Charge.MedType = MedType.MedType 
   WHERE (Charge.SendStatus = 1) AND (Charge.MedType <> 24 AND Charge.MedType <> 34 
   AND Charge.MedType <> 11 AND Charge.MedType <> 12 AND Charge.MedType <> 13 AND Charge.MedType <> 14 
   AND Charge.MedType<>82) AND (Charge.SendTime > '2018-05-16 00:00:00') 
   AND (Charge.SendTime < '2018-05-16 23:59:59') 
   GROUP BY Charge.SendUserID, SendUser.user_name 
我找到在第二段出现错误,为什么出错啊?找不到原因
SELECT '配方' AS ExitStatus, COUNT(DISTINCT Charge.ID) AS Pages 第一个查询,第二个列是整数型的,因为是执行的count运算,union的第二个查询,第二个列是SELECT '电子处方' AS MedTypeName, '配方' AS ExitStatus 字符串'配方',sql会将对应的第二个列转换成第一个查询的第二个列的数据类型,也就是int型,所以就会报错将varchar值'配方'转换成int型失败了[/quote] 不是不是,我调换了顺序,你看#3,但是还是报错[/quote] 你把union全部去掉,然后一起执行一下,看看各个列是否对应

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧