title: DB2 SQL: Join Table Function with Recursive CTE on More Than One Row (Split Strings)
body: <p>On IBM DB2 V10 I have created following function. This function contains a recursive common table expression. It splits a text string, for example <code>'abc,def,ghi'</code>, into a table with three rows <code>'abc'</code>,<code>'def'</code> and <code>'ghi'</code>.</p>
  
  <pre><code>CREATE FUNCTION TOOLS.SPLIT (
      TEXT    VARCHAR(32704),
      SPLIT   VARCHAR(16) )
    RETURNS TABLE (WRD VARCHAR(128))
    LANGUAGE SQL
    CALLED ON NULL INPUT
    READS SQL DATA
    DETERMINISTIC
    NO EXTERNAL ACTION
  RETURN 
    WITH SPLIT_TBL(WORD, SENTENCE, CNT) AS (    
    SELECT CAST(NULL AS VARCHAR(128)) WORD
          ,TEXT SENTENCE   
          ,0 CNT
    FROM SYSIBM.SYSDUMMY1      
    UNION ALL      
    SELECT CASE WHEN INSTR(S1.SENTENCE, SPLIT) &gt; 0
             THEN LEFT( S1.SENTENCE,
                        INSTR(S1.SENTENCE, SPLIT) - MIN(1, LENGTH(SPLIT)) )
             ELSE S1.SENTENCE
           END AS WORD
          ,CASE WHEN INSTR(S1.SENTENCE, SPLIT) &gt; 0
                 AND LENGTH(S1.SENTENCE) &gt; 1
             THEN SUBSTR( S1.SENTENCE,
                          INSTR(S1.SENTENCE, SPLIT) + MAX(LENGTH(SPLIT), 1) )
             ELSE CAST(NULL AS CHAR(128))          
           END AS SENTENCE
          ,CNT + 1 CNT
    FROM SPLIT_TBL S1   
    WHERE S1.SENTENCE IS NOT NULL
      AND S1.CNT &lt; 32704 )  
  
  SELECT WORD
  FROM SPLIT_TBL
  WHERE WORD IS NOT NULL;
  </code></pre>
  
  <p>Basically, this function works as expected on single strings.</p>
  
  <pre><code>SELECT s.*, val.*
  FROM TABLE (
    SELECT '123,456' sntnc, ',' splt FROM SYSIBM.SYSDUMMY1
  ) s
  LEFT JOIN TABLE (
    TOOLS.SPLIT(s.sntnc, s.splt)
  ) val ON 1=1;
  </code></pre>
  
  <p>Produces following table:</p>
  
  <pre><code>SNTNC   | SPLT | WRD
  --------|------|----
  123,456 |    , | 123
  123,456 |    , | 456
  </code></pre>
  
  <p>However, as soon as there is a table with more than one row to be joined, DB2 returns <code>ERROR [42997] [IBM][DB2] SQL0270N  Function not supported (Reason code = "").</code> This means, if I executed following query with more than one select statement not commented out, this error is returned. Though, each single row by itself does not produce an error.</p>
  
  <pre><code>SELECT s.*, val.*
  FROM TABLE (
  --  SELECT CAST(NULL AS CHAR) sntnc, ',' splt FROM SYSIBM.SYSDUMMY1
  --  UNION ALL
  --  SELECT '' sntnc, ',' splt FROM SYSIBM.SYSDUMMY1
  --  UNION ALL
  --  SELECT 'AB' sntnc, CAST(NULL AS CHAR) splt FROM SYSIBM.SYSDUMMY1
  --  UNION ALL
    SELECT ',' sntnc, ',' splt FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT ',ABC,DEF,/' sntnc, '' splt FROM SYSIBM.SYSDUMMY1
  --  UNION ALL
  --  SELECT ',ABC,ADEF,/' sntnc, ',A' splt FROM SYSIBM.SYSDUMMY1
  --  UNION ALL
  --  SELECT '123,456,, ' sntnc, ',' splt FROM SYSIBM.SYSDUMMY1
  --  UNION ALL
  --  SELECT ',,,' sntnc, ',' splt FROM SYSIBM.SYSDUMMY1
  ) s
  LEFT JOIN TABLE (
    TOOLS.SPLIT(s.sntnc, s.splt)
  ) val ON 1=1;
  </code></pre>
  
  <p>On IBM Knowledge Center, I could not find a reason, why such a <a href="http://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_sql_createfunctionsqltable.dita" rel="nofollow">table function</a> cannot be joined on more than one row. <a href="http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.messages.sql.doc/doc/msql00270n.html" rel="nofollow">Error SQL0270N</a> (Function not supported), without a reason code, appears not too helpful either.</p>
  
  <blockquote>
    <p>The statement cannot be processed because it violates a restriction as indicated by the following reason code:</p>
  </blockquote>
  
  <p>I would like to join this function on a subquery or table with more than one row. However, without a useful reason code, I cannot tell what the problem might be. Has anyone an idea what might go wrong when joining more than one row?</p>