Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2010
    Posts
    3

    Unanswered: Recursive CTE in a stored proc

    I have a need to run a recursive CTE within a stored proc, but I can't get it past this:
    SQL0104N An unexpected token "with" was found following "SET count=count+1;
    ". Expected tokens may include: "<psm_while>". LINE NUMBER=26.

    My google-fu showed a couple of similar topics, but none with resolution.

    The query functions as expected outside of the stored proc, so I'm hoping that there's some syntactic sugar I'm missing that'll let this work. Similarly, the proc compiles and works without the query.

    Here's a contrived example:
    --setup
    create table tree (id integer, name varchar(50), parent_id integer);
    insert into tree values (1, 'Alice', null);
    insert into tree values (2, 'Bob', 1);
    insert into tree values (3, 'Charlie', 2);

    -- the proc
    create or replace procedure testme() RESULT SETS 1 LANGUAGE SQL
    BEGIN
    DECLARE SQLSTATE CHAR(5);
    DECLARE SQLCODE integer default 0;
    DECLARE count INTEGER;
    DECLARE sum INTEGER;
    DECLARE total INTEGER;
    DECLARE id INTEGER;
    DECLARE curs CURSOR WITH RETURN FOR
    select count,sum from sysibm.sysdummy1;

    DECLARE hiercurs CURSOR FOR
    select id from tree order by id;
    SET bomQuery='';
    PREPARE stmt FROM bomQuery;
    SET count = 0;
    SET sum = 0;
    set total = 0;
    OPEN hiercurs;
    FETCH hiercurs INTO id;
    WHILE (SQLCODE <> 100) DO
    SET count=count+1;

    with org (level,id,name,parent_id) as
    (select 1 as level,root.id,root.name,root.parent_id from tree root where root.id=id
    union all
    select level+1,employee.id,employee.name,employee.parent_ id from org boss, tree employee
    where level < 5 and employee.parent_id=boss.id)
    select count(1) into sum from org;

    SET total=total+sum;
    FETCH hiercurs INTO id;
    END WHILE;
    CLOSE hiercurs;
    OPEN curs;
    END/

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1) You can't use common table expressions(WITH ... ) in a SELECT INTO statement.
    You can use it in a DECLARE cursor statement.

    2) Your cursor hiercurs can be combined with curs.
    I have never seen a necessity to use cursor except returning final results or working with resources outside RDB(typically os files).

    3) I guessed that you might want to use total instead of sum in the following statement.
    DECLARE curs CURSOR WITH RETURN FOR
    select count,sum from sysibm.sysdummy1;

    4) It will be better to use other than slash("/") for a statement termination character.
    Because slash has some special usage in SQL, like bracketing comments(/* ... */), divide operator, date format('mm/dd/yyyy') and so on.


    Here is an example considering above notes:
    Code:
    create or replace procedure testme()
     RESULT SETS 1
     LANGUAGE SQL
    
    BEGIN
    DECLARE curs CURSOR WITH RETURN FOR
    WITH org (level , id , name , parent_id) as (
    SELECT 1 as level
         , root.id , root.name , root.parent_id
      FROM tree root
    /* where root.id = id
     select id from tree order by id
    */
    UNION ALL
    SELECT level + 1
         , employee.id , employee.name , employee.parent_id
      FROM org  boss
         , tree employee 
     WHERE level < 5
       and employee.parent_id = boss.id
    )
    SELECT COUNT(*) AS count
         , SUM(sum) AS total
      FROM (SELECT COUNT(1) AS sum
              FROM org
             GROUP BY id
           ) q
    ;
    
    OPEN curs;
    END!
    Last edited by tonkuma; 07-20-10 at 00:02. Reason: Updated sample code:(1) Removed root_id from CTE columns, (2) Update final SELECT.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can make the routine as a table function, too.

    Sample SQL table function:
    Code:
    ------------------------------ Commands Entered ------------------------------
    create or replace FUNCTION testme()
     RETURNS TABLE
             (count INTEGER , total INTEGER)
     LANGUAGE SQL
    RETURN
    WITH org (level , id , name , parent_id) as (
    SELECT 1 as level
         , root.id , root.name , root.parent_id
      FROM tree root
    /* where root.id = id
     select id from tree order by id
    */
    UNION ALL
    SELECT level + 1
         , employee.id , employee.name , employee.parent_id
      FROM org  boss
         , tree employee 
     WHERE level < 5
       and employee.parent_id = boss.id
    )
    SELECT COUNT(*) AS count
         , SUM(sum) AS total
      FROM (SELECT COUNT(1) AS sum
              FROM org
             GROUP BY id
           ) q
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Use of the function:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT *
      FROM TABLE( testme() ) AS t
    ;
    ------------------------------------------------------------------------------
    
    COUNT       TOTAL      
    ----------- -----------
              3           6
    
      1 record(s) selected.
    Last edited by tonkuma; 07-20-10 at 00:05. Reason: Updated function body: (1) Removed root_id from CTE columns, (2) Updated final SELECT.

  4. #4
    Join Date
    May 2010
    Posts
    21
    Quote Originally Posted by rgrbrny View Post
    with org (level,id,name,parent_id) as
    (select 1 as level,root.id,root.name,root.parent_id from tree root where root.id=id
    union all
    select level+1,employee.id,employee.name,employee.parent_ id from org boss, tree employee
    where level < 5 and employee.parent_id=boss.id)
    select count(1) into sum from org;
    I have never tried to insert from a with before, but I did make a function which returned a table. I learned from that that sometimes what can appear correct is not so. I had to do like the following:

    Select field1 from table(myschema.myTableCreator()).
    My original query was simply:
    Select field1 from myschema.myTableCreator().

    It looked correct, and I was certain that the function itself was validly defined because it was a rather simple select from another table and the create function ran, and what seemed to be a rather obvious and noted capability (functions can return tables) was ellusive.

    My advise: I do not know if DB2 disallows using CTEs/WITH as a select into, but given my past experience, it allows quite a bit. (You can declare identically named functions that take different parameters which MS SQL disallows). And the documentation does not specifically mention disallowing WITH on INSERT INTO: IBM Information Management Software for z/OS Solutions Information Center

    Try to create a simple test case/table without WITH and try inserting from it to your table, if that works; then play with it a bit. It may or may not be disallowed, but I really see little reason for disallowing it as when you really think about it, we work with temporary tables all the time and CTEs are nothing more than temporary tables that allow recursive references. You do use some recursion, but you place a limit on the depth of the calls, so I do not see why DB2 should disallow it. DB2 WILL disallow certain things where a cycle is possible, but my understanding of WITH was that Cycles only produce warnings rather than errors, as such you may end up with a query that has to be killed, but it should run.

    See the following article on with and recursion:
    http://www.ibm.com/developerworks/da...dm-0510rielau/
    Last edited by jkuyken; 07-20-10 at 18:29.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    And the documentation does not specifically mention disallowing WITH on INSERT INTO: IBM Information Management Software for z/OS Solutions Information Center
    You are right!
    Syntax of DB2 Version 9.1 for z/OS allows WITH on SELECT INTO statement: IBM Information Management Software for z/OS Solutions Information Center

    DB2 UDB for z/OS Version 8 doesn't support WITH on SELECT INTO statement: IBM Information Management Software for z/OS Solutions Information Center

    DB2 9.7 for LUW doesn't support WITH on SELECT INTO statement: http://publib.boulder.ibm.com/infoce.../r0000994.html
    Last edited by tonkuma; 07-20-10 at 19:09. Reason: Add a link to DB2 9.7 for LUW Information Center.

  6. #6
    Join Date
    Jul 2010
    Posts
    3

    Meh...db2

    the cte in db2 doesn't seem to recognize the scalar result of the query, and so it won't let the select into work (not a problem on Oracle or SQLServer)...my solution is to open a cursor and FETCH INTO (instead of SELECT INTO) instead.

    The other solutions miss the point of the question, but thanks for the effort regardless.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •