概述
在SQL server环境查询的时候，出现，从数据类型 varchar 转换为 numeric 时出错。
报错图片
先说解决办法
Select cast(字段 as 数据类型) from 表名
由于数字溢出，那么就需要用到cast，将这两个数字溢出的字段转化为字符型，这样就不会出现溢出的情况了。
CAST(RA.OBJECT_MODULE_ID AS varchar(30)),CAST(RA.TARGET_MODULE_ID AS varchar(30)),
SELECTR.*,'' AS TARGET_NAME,'' AS PRIMARY_NAME,'' AS TARGET_STATUS,'' AS TYPE,'' AS DETAIL_ID,'' AS DETAIL_NAME,'' AS DETAIL_CONTENT,CAST(RA.OBJECT_MODULE_ID AS varchar(30)),CAST(RA.TARGET_MODULE_ID AS varchar(30)),'' AS IS_DELETE FROMRELEVANCE RRIGHT JOIN RELEVANCE_APP RA ON R.ID = RA.RELEVANCE_ID
自定义字段
如果这个字段是一个自定义字段，这样的方式就会报错
找不到这个字段，那么就需要这样写。
select *,cast('自定义的值' as 自定义的字段类型) as '列名' from 表名
错误的执行sql
两个查询分开查询都没有问题，但是合在一起查询就出现从数据类型 varchar 转换为 numeric 时出错。原因就是数字溢出了，具体怎么溢出的呢，大概就是SQL server会自动把这个字段先转为数字，然后进行一系列的匹配。
SELECTR.*,'' AS TARGET_NAME,'' AS PRIMARY_NAME,'' AS TARGET_STATUS,'' AS TYPE,'' AS DETAIL_ID,'' AS DETAIL_NAME,'' AS DETAIL_CONTENT,'' AS OBJECT_MODULE_ID,'' AS TARGET_MODULE_ID,'' AS IS_DELETE FROMRELEVANCE R WHERER.TARGET_MODULE IN ( 'customer', 'saleChance', 'contract', 'production', 'competitor', 'contact', 'kpiFlow', 'marketactivity', 'clue', 'orderform', 'price', 'capital', 'quote' ) UNIONSELECTR.*,'' AS TARGET_NAME,'' AS PRIMARY_NAME,'' AS TARGET_STATUS,'' AS TYPE,'' AS DETAIL_ID,'' AS DETAIL_NAME,'' AS DETAIL_CONTENT,RA.OBJECT_MODULE_ID,RA.TARGET_MODULE_ID,'' AS IS_DELETE FROMRELEVANCE RRIGHT JOIN RELEVANCE_APP RA ON R.ID = RA.RELEVANCE_ID
最后就成功解决了这个问题。
