我已经写了一个windows批处理脚本,从POS系统下载库存,然后将其上传到另一个文件。我在上传过程中一直收到错误,我最终发现这是因为在标题下面的txt文件中生成了连字符:
sku quantity
------------------------------
1234 1我可以使用switch -h-1删除整个标头,但是在尝试重新添加标头(没有连字符)时,我遇到了数据转换错误。请参阅下面的sql。错误消息为:“将数据类型varchar转换为numeric时出错”,第3行
set nocount on
SELECT 'sku' AS sku, 'quantity' AS sku
UNION
SELECT CAST(IM_BARCOD.BARCOD AS VARCHAR(32)) as sku, case when
IM_INV.QTY_AVAIL > 0 then IM_INV.QTY_AVAIL else CAST(0 AS VARCHAR(32)) END as
quantity
FROM IM_BARCOD INNER JOIN IM_INV ON IM_INV.ITEM_NO = IM_BARCOD.ITEM_NO
INNER JOIN
IM_PRC ON IM_INV.ITEM_NO = IM_PRC.ITEM_NO INNER JOIN
IM_ITEM ON IM_INV.ITEM_NO = IM_ITEM.ITEM_NO
WHERE (IM_ITEM.TRK_METH = 'N' AND IM_INV.LOC_ID = 'MAIN')
UNION
SELECT CAST(IM_BARCOD.BARCOD AS VARCHAR(32)) as sku, case when
IM_INV_CELL.QTY_AVAIL > 0 then IM_INV_CELL.QTY_AVAIL else CAST(0 AS
VARCHAR(32)) END as quantity
FROM IM_BARCOD INNER JOIN IM_PRC ON IM_BARCOD.ITEM_NO = IM_PRC.ITEM_NO
INNER JOIN
IM_INV_CELL ON IM_BARCOD.ITEM_NO = IM_INV_CELL.ITEM_NO AND
IM_INV_CELL.DIM_1_UPR=IM_BARCOD.DIM_1_UPR AND
IM_INV_CELL.DIM_2_UPR=IM_BARCOD.DIM_2_UPR AND
IM_INV_CELL.DIM_3_UPR=IM_BARCOD.DIM_3_UPR INNER JOIN
IM_ITEM ON IM_BARCOD.ITEM_NO = IM_ITEM.ITEM_NO
WHERE (IM_ITEM.TRK_METH = 'G' AND IM_INV_CELL.LOC_ID = 'MAIN')但是,我正在尝试将数值字段转换为varchar。有什么想法吗?我是基于stack overflow的另一篇文章来尝试这个的。
谢谢!
发布于 2018-07-13 01:54:51
在这一行中:
case when IM_INV.QTY_AVAIL > 0 then IM_INV.QTY_AVAIL else CAST(0 AS VARCHAR(32)) END您需要在THEN子句中将qty_avail转换为varchar(32)。否则,它将尝试将该列隐式转换为数字数据类型。
https://stackoverflow.com/questions/51311675
复制相似问题