SQL视图用union all查询显示varchar转换numeric类型错误

最新推荐文章于 2023-05-06 17:41:55 发布

慢慢的博客

最新推荐文章于 2023-05-06 17:41:55 发布

阅读量1.7k

点赞数

文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/qq_45610205/article/details/121557908

版权

视图中的两部分分开查询都没问题

> SELECT 
'C'+CAST(C.DH AS VARCHAR(20))+CAST(C.ID AS VARCHAR(20)) AS CHARGE_NO,
'1' AS CHARGE_FLAG,
'1' AS UP_FLAG,CAST(C.ID AS VARCHAR(64))AS LOCAL_ID,CAST(A.ghh AS VARCHAR(64))AS MRN,ISNULL(C.FPH,'-')AS INVOICE_NUM,
'01' AS PAY_TYPE,
'' AS PAY_NUM,
'100' AS MED_TYPE,
A.sfsj AS UP_TIME
FROM
mz_db..mz_next_qycf_sfxm A LEFT JOIN
mz_db..mz_fpdyb C ON A.GHH=C.GHH LEFT JOIN 
mz_db..dmb_ksdm D ON A.kjks=D.KSDM LEFT JOIN 
ylbx_neu..mz_main_yb_neu B ON A.mzh=B.MZH
WHERE A.SFSJ>=@SJ
UNION ALL
SELECT
'S'+ltrim(CAST(A.SFDH AS VARCHAR(20)))+ltrim(cast(B.ID AS VARCHAR(20))) AS CHARGE_NO,
'1' AS CHARGE_FLAG,
'1' AS UP_FLAG,
CAST(A.ghh AS VARCHAR(64))AS LOCAL_ID,
CAST(A.mzh AS VARCHAR(64))AS MRN,
ISNULL(B.FPH,'-')AS INVOICE_NUM,
'01' AS PAY_TYPE,
'' AS PAY_NUM,
'100' AS MED_TYPE,
A.sfsj AS UP_TIME
FROM 
mz_db..mz_main_sfdj A LEFT JOIN 
mz_db..MZ_FPDYB B ON A.SFDH=B.DH LEFT JOIN 
MZ_DB..DMB_KSDM C ON A.KJKS=C.KSDM LEFT JOIN 
ylbx_neu..mz_main_yb_neu D ON A.mzh=D.MZH

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

把所有的numeric内容全部cast转换，还是不行，分开查询都能查出来，但是合在一起就出错

后来发现下边的查询和上边的数据类型不一样，这种的查询都是需要上边类型和下边一样的，把下边那个cast转换成varchar类型就没问题了