gpt4 book ai didi

SQL Server 2008 : PIVOT throwing an error 'The conversion of the varchar value ' xxx' overflowed an int column.'

转载 作者:行者123 更新时间:2023-12-02 08:52:59 26 4
gpt4 key购买 nike

在使用 UDF 将 CSV 字段转换为表后,我正在使用 SQL Server 2008 PIVOT 函数。

为了显示此错误,我消除了第一步,即将 CSV 字段交叉应用到 UDF 以获取我在下面的代码示例中手动创建的表.

CREATE TABLE #TCMDRES (
SYS_NR BIGINT,
DAT_TE VARCHAR(150),
ID CHAR(1)
)

-- ,,,,,,WIN ASDF v2,20100406.BAK,32515325772,32514331136,
-- After being separated and put into columns 1-11...
INSERT INTO #TCMDRES
VALUES(1,'','X'),(2,'','X'),(3,'','X'),(4,'','X'),(5,'','X'),(6,'','X'),(7,'WIN ASDF v2','X'),
(8,'20100406.BAK','X'),(9,'32515325772','X'),(10,'32514331136','X'),(11,'','X')

SELECT *
FROM #TCMDRES

-- This errors out with:
-- Msg 248, Level 16, State 1, Line 16
-- The conversion of the nvarchar value '32514331136' overflowed an int column.
SELECT ID, [8] AS FIL_NA, [10] AS FIL_SZ_NR
FROM
(
SELECT DAT_TE, SYS_NR, ID
FROM #TCMDRES a
) a
PIVOT (MAX(DAT_TE) FOR SYS_NR IN ([8],[10])) AS pvt
WHERE [10] <> 0

DELETE FROM #TCMDRES

-- Try again but drop the last two digits from column 10...
INSERT INTO #TCMDRES
VALUES(1,'','X'),(2,'','X'),(3,'','X'),(4,'','X'),(5,'','X'),(6,'','X'),(7,'WIN ASDF v2','X'),
(8,'20100406.BAK','X'),(9,'32515325772','X'),(10,'325143311','X'),(11,'','X')

SELECT ID, [8] AS FIL_NA, [10] AS FIL_SZ_NR
FROM
(
SELECT DAT_TE, SYS_NR, ID
FROM #TCMDRES a
) a
PIVOT (MAX(DAT_TE) FOR SYS_NR IN ([8],[10])) AS pvt
WHERE [10] <> 0

DROP TABLE #TCMDRES

总而言之,如果您尝试对较大值进行PIVOT,即使它存储为 VARCHAR,尝试将其转换为 INT< 也会失败。/.

有人对如何解决这个问题或者是否可以解决这个问题有任何想法吗?似乎是 PIVOT 功能的限制...

最佳答案

这是你的WHERE条款。 0INT 。试试这个(为我测试过):

WHERE [10] <> CAST(0 as bigint)

或者,按照Lamak :

另一种方式是 WHERE [10] <> '0'

关于SQL Server 2008 : PIVOT throwing an error 'The conversion of the varchar value ' xxx' overflowed an int column.',我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/7166450/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com