"VALUE ... IS TOO LONG" error in a recursive query that concatenates characters

Question

While executing this query

     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)

I'm getting this error:

VALUE AB IS TOO LONG SQL Code: -433, SQL State: 22001

I enabed trace within the IBM Data Studio, but it did not provide any additional details:

[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

What might be causing the error?

on DB2, `VALUES(...)` can be used to return multiple rows (and you don't need the reference to `sysdummy`). You don't need the `C` table reference at all - no further rows will be returned once there is no "next" row. Oh, and the preferred syntax is to explicitly specify `JOIN` s - please don't use the implicit-join (comma-separated `FROM` clause) syntax. — Clockwork-Muse, Nov 21 '15 at 08:23

score 1 · Answer 1 · answered Nov 20 '15 at 15:17

1

Result set attributes of a recursive query are determined based on the first subselect, so in this case:

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 is VARCHAR(1), so when you attempt to assign a longer value to it in the recursive part, it fails.

You will need to explicitly cast DOC_STRING to the appropriate type:

B (DOC_STRING,ROW_NUM) AS 
(SELECT CAST(DOC_STRING AS VARCHAR(140)),ROW_NUM 
   FROM A
 ...

answered Nov 20 '15 at 15:17

mustaccio

18,234
16
48
57

Thanks mustaccio, I've changed the query according to you comment but it's still in error: VALUE ABBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB IS TOO LONG SQL Code: -433, SQL State: 22001 – Nik Nov 20 '15 at 16:09
Your query seems to have an infinite loop in it, so after 140 iterations the concatenation result won't fit into `VARCHAR(140)`. – mustaccio Nov 20 '15 at 16:25
Changing the second query in SELECT CONCAT (B.DOC_STRING,A.DOC_STRING) ,B.ROW_NUM + 1 FROM A,B,C WHERE A.ROW_NUM = B.ROW_NUM + 1 AND B.ROW_NUM <= C.MAX_NUM it worked... I'm still having warning sqlcode 347 but the result is what I expected to be... Thank for you help! – Nik Nov 20 '15 at 16:47

"VALUE ... IS TOO LONG" error in a recursive query that concatenates characters

1 Answers1