从数据类型 varchar 转换为 numeric 时出错

概述

在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)),

SELECT
	R.*,
	'' 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 
FROM
	RELEVANCE R
	RIGHT JOIN RELEVANCE_APP RA ON R.ID = RA.RELEVANCE_ID

自定义字段

如果这个字段是一个自定义字段，这样的方式就会报错

找不到这个字段，那么就需要这样写。

select *,cast('自定义的值' as 自定义的字段类型) as '列名' from 表名

错误的执行sql

两个查询分开查询都没有问题，但是合在一起查询就出现从数据类型 varchar 转换为 numeric 时出错。原因就是数字溢出了，具体怎么溢出的呢，大概就是SQL server会自动把这个字段先转为数字，然后进行一系列的匹配。

SELECT
	R.*,
	'' 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 
FROM
	RELEVANCE R 
WHERE
	R.TARGET_MODULE IN ( 'customer', 'saleChance', 'contract', 'production', 'competitor', 'contact', 'kpiFlow', 'marketactivity', 'clue', 'orderform', 'price', 'capital', 'quote' ) UNION
SELECT
	R.*,
	'' 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 
FROM
	RELEVANCE R
	RIGHT JOIN RELEVANCE_APP RA ON R.ID = RA.RELEVANCE_ID

最后就成功解决了这个问题。