Select case when IsNumeric(Total_Arrear) = 1 then Total_Arrear Else 0 end From Revenue_Orig你是在插入语句中吧,要被插入的字段类型定义为:NUMERIC(28,6) 因为你的值420800456.51无法转换成INT类型.
试试 Select case when IsNumeric(Total_Arrear) = 1 then cast(Total_Arrear as real) Else 0 end From Revenue_Orig
SELECT au_fname, au_lname, CASE state WHEN 'CA' THEN 'California' WHEN 'KS' THEN 'Kansas' WHEN 'TN' THEN 'Tennessee' WHEN 'OR' THEN 'Oregon' WHEN 'MI' THEN 'Michigan' WHEN 'IN' THEN 'Indiana' WHEN 'MD' THEN 'Maryland' WHEN 'UT' THEN 'Utah' END AS StateName FROM pubs.dbo.authors ORDER BY au_lname
呵呵,谢谢大家,刚刚去吃了一个同事的生日蛋糕,回来就找到原因了:是 字段中有 - 号,而cas when then ...中 then后面的数据默认为int,而将-转换为int时就出现错误。现在用这样的一条语句解决了(不能用Real,因为-只有转换为money型才不会出错)。Select Case When isnumeric(字段)then convert(money,字段) else 0 end From 表谢谢大家。
因为你的值420800456.51无法转换成INT类型.
Select case when IsNumeric(Total_Arrear) = 1 then cast(Total_Arrear as real) Else 0 end From Revenue_Orig
CASE state
WHEN 'CA' THEN 'California'
WHEN 'KS' THEN 'Kansas'
WHEN 'TN' THEN 'Tennessee'
WHEN 'OR' THEN 'Oregon'
WHEN 'MI' THEN 'Michigan'
WHEN 'IN' THEN 'Indiana'
WHEN 'MD' THEN 'Maryland'
WHEN 'UT' THEN 'Utah'
END AS StateName
FROM pubs.dbo.authors
ORDER BY au_lname