错误'将数据类型varchar转换为数字时出错' - 创建新表
问题描述:
我有一个表“Table1_201403”,带有1个varchar列'列0'。现在,我想从这个表下面的查询插入必要的数据Master_Table2 -错误'将数据类型varchar转换为数字时出错' - 创建新表
SELECT '2014/03' AS Period
,ltrim(rtrim(SUBSTRING([Column 0], 1, 12))) AS 'Account'
,cast(ltrim(rtrim(SUBSTRING([Column 0], 17, 2))) + ltrim(rtrim(SUBSTRING([Column 0], 15, 2))) + ltrim(rtrim(SUBSTRING([Column 0], 13, 2))) AS DATE) AS Trans_Date
,CASE
WHEN ltrim(rtrim(SUBSTRING([Column 0], 30, 1))) = '-'
THEN CAST((CAST(ltrim(rtrim(SUBSTRING([Column 0], 19, 11))) AS DECIMAL)/100) AS DECIMAL(9, 2)) * - 1
ELSE CAST((CAST(ltrim(rtrim(SUBSTRING([Column 0], 19, 11))) AS DECIMAL)/100) AS DECIMAL(9, 2))
END AS Amount
INTO Master_Table2
FROM Table1_201403
WHERE SUBSTRING([Column 0], 11, 1) <> 'B'
当我尝试这样做,我得到的错误“错误转换数据类型为varchar到数字”。
什么可以是一个合适的解决方案。任何帮助将不胜感激。
谢谢。
改性 -
SELECT '2014/03' AS Period
,ltrim(rtrim(SUBSTRING([Column 0], 1, 12))) AS 'GL Account'
,cast(ltrim(rtrim(SUBSTRING([Column 0], 17, 2))) + ltrim(rtrim(SUBSTRING([Column 0], 15, 2))) + ltrim(rtrim(SUBSTRING([Column 0], 13, 2))) AS DATE) AS Trans_Date
,case
when ltrim(rtrim(SUBSTRING([Column 0], 30, 1))) = '-'
THEN(
case when isnumeric(ltrim(rtrim(SUBSTRING([Column 0], 19, 11))))=1 then
CAST(CAST((CAST(ltrim(rtrim(SUBSTRING([Column 0],19,11))) AS decimal)/100) AS decimal(9,2)) AS decimal)*-1
else
CAST(CAST((CAST(ltrim(rtrim(SUBSTRING([Column 0],19,11))) AS decimal)/100) AS decimal(9,2)) AS varchar)*-1 end
)
ELSE(
case when isnumeric(ltrim(rtrim(SUBSTRING([Column 0], 19, 11))))=1 then
CAST(CAST((CAST(ltrim(rtrim(SUBSTRING([Column 0],19,11))) AS decimal)/100) AS decimal(9,2)) AS decimal)
else
CAST(CAST((CAST(ltrim(rtrim(SUBSTRING([Column 0],19,11))) AS decimal)/100) AS decimal(9,2)) AS varchar) end
)
END as Amount
INTO TABLE2
FROM TABLE1
WHERE SUBSTRING([Column 0], 11, 1) <> 'B'
我尝试使用则IsNumeric逻辑。但无法让代码工作。请看一看。 @AdiŤ
并且,数据使用脚本@t_m是如下得到 -
列0 ValidDecimal 006125 01031400000451745+ JNLCIS批次2014年1月3日ÑBA0119 CL 1个 006125 01031400000212077+ JNLCIS批次01/03/2014 N BA0120 CL 1 006125 01031400000311569+ JNLCIS Batch 01/03/2014 N BA0119 PL 1
答
您试图将数据从varchar转换为decimal.if数据属于你无法转换为十进制的字符。
use cast() or convert() function
答
怀疑Amount逻辑可能返回一些varchar值而不是小数。
运行下面的脚本来检查您的金额逻辑是否正确。运行脚本时,如果一切正常,则不应返回任何记录。
select *
from
(
select '2014/03' as Period,
ltrim(rtrim(SUBSTRING([Column 0],1,12))) as 'Account',
cast(ltrim(rtrim(SUBSTRING([Column 0],17,2)))+ltrim(rtrim(SUBSTRING([Column 0],15,2)))+ltrim(rtrim(SUBSTRING([Column 0],13,2))) AS DATE)
as Trans_Date,
ISNUMERIC(case when ltrim(rtrim(SUBSTRING([Column 0],30,1)))='-' then CAST((CAST(ltrim(rtrim(SUBSTRING([Column 0],19,11))) AS decimal)/100) AS decimal(9,2))*-1
else CAST((CAST(ltrim(rtrim(SUBSTRING([Column 0],19,11))) AS decimal)/100) AS decimal(9,2)) END) As ValidDecimal
from Table1_201403
where SUBSTRING([Column 0],11,1) <> 'B'
) CheckForValidDecimal
where ValidDecimal = 0
修改:
运行下面这个脚本来找到你的[专栏0]是不正确的记录。然后,您可以更改您的金额逻辑来解决问题。
select *
from
(
select
[Column 0],
ISNUMERIC(case when ltrim(rtrim(SUBSTRING([Column 0],30,1)))='-' then CAST((CAST(ltrim(rtrim(SUBSTRING([Column 0],19,11))) AS decimal)/100) AS decimal(9,2))*-1
else CAST((CAST(ltrim(rtrim(SUBSTRING([Column 0],19,11))) AS decimal)/100) AS decimal(9,2)) END) As ValidDecimal
from Table1_201403
where SUBSTRING([Column 0],11,1) <> 'B'
) CheckForValidDecimal
where ValidDecimal = 0
答
请尝试这种方式
SELECT '2014/03' AS Period
,ltrim(rtrim(SUBSTRING([Column 0], 1, 12))) AS 'Account'
,cast(ltrim(rtrim(SUBSTRING([Column 0], 17, 2))) + ltrim(rtrim(SUBSTRING([Column 0], 15, 2))) + ltrim(rtrim(SUBSTRING([Column 0], 13, 2))) AS DATE) AS Trans_Date
,CASE
WHEN ltrim(rtrim(SUBSTRING([Column 0], 30, 1))) = '-'
THEN case when isnumeric(case when isnumeric(ltrim(rtrim(SUBSTRING([Column 0], 19, 11))
))=1 then
CAST(ltrim(rtrim(SUBSTRING([Column 0], 19, 11)))AS decimal) else
CAST(ltrim(rtrim(SUBSTRING([Column 0], 19, 11)))AS varchar) end)=1
then CAST(case when isnumeric(ltrim(rtrim(SUBSTRING([Column 0], 19, 11))
))=1 then
CAST(ltrim(rtrim(SUBSTRING([Column 0], 19, 11)))AS decimal) else
CAST(ltrim(rtrim(SUBSTRING([Column 0], 19, 11)))AS varchar) end AS decimal(9,2)) *-1
else
CAST(ltrim(rtrim(SUBSTRING([Column 0], 19, 11)))AS varchar)
ELSE
CAST(case when isnumeric(ltrim(rtrim(SUBSTRING([Column 0], 19, 11))
))=1 then
CAST(ltrim(rtrim(SUBSTRING([Column 0], 19, 11)))AS decimal) else
CAST(ltrim(rtrim(SUBSTRING([Column 0], 19, 11)))AS varchar) end AS decimal(9,2)) /100 AS DECIMAL
else
CAST(ltrim(rtrim(SUBSTRING([Column 0], 19, 11)))AS varchar)
END
INTO Master_Table2
FROM Table1_201403
WHERE SUBSTRING([Column 0], 11, 1) <> 'B'
'什么可以适当solution.'没有错误消息告诉你吗?停止尝试将一个varchar插入到数字字段中。使用CAST或CONVERT将其转换为数字。 – 2014-08-29 17:49:10
我试图将数字转换为数字,但没有帮助。你能不能进一步帮助我? – 0nir 2014-08-29 18:00:14
使用IsNumeric逻辑编辑代码但不起作用。 – 0nir 2014-08-29 19:05:11