DB2 sql递归连接错误
DB2 sql递归连接错误
提问于 2015-11-20 19:59:44
我没有找到任何类似的东西，所以我将解释我的问题……
在执行此查询时
WITH A (DOC_STRING,ROW_NUM) AS
(SELECT CAST('A' AS VARCHAR(1)),1
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT CAST('B' AS VARCHAR(1)),2
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT CAST ('C' AS VARCHAR(1)),3
FROM SYSIBM.SYSDUMMY1 )
,C (MAX_NUM) AS
(SELECT COUNT(*) FROM A)
,B (DOC_STRING,ROW_NUM) AS
(SELECT DOC_STRING,ROW_NUM
FROM A
WHERE ROW_NUM = 1
UNION ALL
SELECT CAST (
CONCAT (B.DOC_STRING,A.DOC_STRING)
AS VARCHAR(140))
,B.ROW_NUM
FROM A,B,C
WHERE A.ROW_NUM = B.ROW_NUM + 1
AND A.ROW_NUM <= C.MAX_NUM
)
SELECT * FROM B
WHERE ROW_NUM = (SELECT MAX(ROW_NUM) FROM A)我收到错误:值AB太长SQL代码：-433，SQL状态: 22001
我在IBM Data Studio中启用了跟踪：
[jcc] BEGIN TRACE_DIAGNOSTICS
[jcc][Thread:Worker-28][SQLWarning@aaef651f] java.sql.SQLWarning
[jcc][Thread:Worker-28][SQLWarning@aaef651f][Sqlca@8c3bf3fb] DB2 SQLCA from server
[jcc][Thread:Worker-28][SQLWarning@aaef651f][Sqlca@8c3bf3fb] SqlCode = 347
[jcc][Thread:Worker-28][SQLWarning@aaef651f][Sqlca@8c3bf3fb] SqlErrd = { 0, 0, 53333, 1127202000, 0, 0 }
[jcc][Thread:Worker-28][SQLWarning@aaef651f][Sqlca@8c3bf3fb] SqlErrmc = B
[jcc][Thread:Worker-28][SQLWarning@aaef651f][Sqlca@8c3bf3fb] SqlErrmcTokens = { B }
[jcc][Thread:Worker-28][SQLWarning@aaef651f][Sqlca@8c3bf3fb] SqlErrp = DSNXODML
[jcc][Thread:Worker-28][SQLWarning@aaef651f][Sqlca@8c3bf3fb] SqlState = 01605
[jcc][Thread:Worker-28][SQLWarning@aaef651f][Sqlca@8c3bf3fb] SqlWarn =
[jcc][Thread:Worker-28][SQLWarning@aaef651f] SQL state = 01605
[jcc][Thread:Worker-28][SQLWarning@aaef651f] Error code = 347
[jcc][Thread:Worker-28][SQLWarning@aaef651f] Tokens = B
[jcc][Thread:Worker-28][SQLWarning@aaef651f] Stack trace follows
[jcc] BEGIN TRACE_DIAGNOSTICS
[jcc][Thread:Worker-28][SQLException@11750c6c] java.sql.SQLException
[jcc][Thread:Worker-28][SQLException@11750c6c][Sqlca@a1af1729] DB2 SQLCA from server
[jcc][Thread:Worker-28][SQLException@11750c6c][Sqlca@a1af1729] SqlCode = -433
[jcc][Thread:Worker-28][SQLException@11750c6c][Sqlca@a1af1729] SqlErrd = { 1046, 0, 0, -1, 0, 0 }
[jcc][Thread:Worker-28][SQLException@11750c6c][Sqlca@a1af1729] SqlErrmc = AB
[jcc][Thread:Worker-28][SQLException@11750c6c][Sqlca@a1af1729] SqlErrmcTokens = { AB }
[jcc][Thread:Worker-28][SQLException@11750c6c][Sqlca@a1af1729] SqlErrp = DSNXRSBC
[jcc][Thread:Worker-28][SQLException@11750c6c][Sqlca@a1af1729] SqlState = 22001
[jcc][Thread:Worker-28][SQLException@11750c6c][Sqlca@a1af1729] SqlWarn =
[jcc][Thread:Worker-28][SQLException@11750c6c] SQL state = 22001
[jcc][Thread:Worker-28][SQLException@11750c6c] Error code = -433
[jcc][Thread:Worker-28][SQLException@11750c6c] Tokens = AB有什么想法吗？
谢谢!
回答 1
Stack Overflow用户
发布于 2015-11-20 23:17:50
递归查询的结果集属性是基于第一个子选择确定的，因此在本例中：
B (DOC_STRING,ROW_NUM) AS
(SELECT DOC_STRING,ROW_NUM -- this one determines result set data types
FROM A
WHERE ROW_NUM = 1
UNION ALL
SELECT CAST (
CONCAT (B.DOC_STRING,A.DOC_STRING)
AS VARCHAR(140))
,B.ROW_NUM
FROM A,B,C
...DOC_STRING是VARCHAR(1)，所以当您试图在递归部分中为它赋一个更长的值时，它会失败。
您需要将DOC_STRING显式转换为适当的类型：
B (DOC_STRING,ROW_NUM) AS
(SELECT CAST(DOC_STRING AS VARCHAR(140)),ROW_NUM
FROM A
...页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/33826213
复制相关文章
相似问题
腾讯云开发者
