Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    155

    Unanswered: Best practise? Recursive sql

    Hallo everyone,

    I dont know if it can be categorized as best practise. I just want to share my experience dealing with recursive sql.

    Code:
    Dont take WITH AS clause as "table" to iterate to another table
    Code:
    Always take concrete tables to do recursive/iteration
    I tried to run recursive between WITH AS-"Table" and a real table. It never stopped. Perhaps because of the group by clause in the WITH AS-"Table"

    Surely this forum has droven me to create a real table from the WITH AS-"Table". Therefore, thank you guys..

    Regards,
    Ratna

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by ratnalein View Post
    Hallo everyone,

    I dont know if it can be categorized as best practise. I just want to share my experience dealing with recursive sql.

    Code:
    Dont take WITH AS clause as "table" to iterate to another table
    Code:
    Always take concrete tables to do recursive/iteration
    I tried to run recursive between WITH AS-"Table" and a real table. It never stopped. Perhaps because of the group by clause in the WITH AS-"Table"

    Surely this forum has droven me to create a real table from the WITH AS-"Table". Therefore, thank you guys..

    Regards,
    Ratna
    The CTE contains the recursive part. What do you mean when you say "Always take concrete tables to do recursive/iteration"? Can you give an example?
    --
    Lennart

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    ratnalein, can you show the SQL for the recursive query you are having problems with? here is an example that doesn't use a real table while doing recursion.
    Code:
    WITH DATE_TAB (DATE_COL)
      AS (SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1)
       , REC_DATE_TAB (COUNTER, DATE_COL)
      AS (SELECT 1, DATE_COL
          FROM DATE_TAB
          UNION ALL
          SELECT COUNTER + 1, DATE_COL + 1 DAY
          FROM REC_DATE_TAB
          WHERE COUNTER + 1 <= 10
         )
    SELECT *
    FROM REC_DATE_TAB
    COUNTER     DATE_COL  
    ----------- ----------
              1 10/23/2012
              2 10/24/2012
              3 10/25/2012
              4 10/26/2012
              5 10/27/2012
              6 10/28/2012
              7 10/29/2012
              8 10/30/2012
              9 10/31/2012
             10 11/01/2012
    I create a CTE that contains CURRENT DATE. I do use SYSIBM.SYSDUMMY1 (a real table) to generate this CTE.
    The second CTE is where the Recursion is processed. I used the first CTE and increment the date by 1 day and stop after 10 iterations.

    Other than SYSIBM.SYSDUMMY, there isn't a 'real' table in the query.

  4. #4
    Join Date
    May 2012
    Posts
    155
    Hallo everyone,

    with WITH AS table I was meaning something like:

    Code:
    WITH
        base1 AS
        (
            SELECT
            	...
            FROM
                 ...
            WHERE
                 ...
        )
    
    ,base as (
    
    SELECT
          ...	
    FROM
          ...
    GROUP BY
          ...	
    )
    
    , rec (...) as (
    select 
          ...	    
    from 
        BASE B
    union all
        select
            ...
        from
            ...    
        where
            ...
    )
    
    select * from rec;
    I run exactly this code, and it never stopped. As we can see, in the recursive part, I took BASE B as "table". I would not suggest that. But it could be okay, if there is no group by in the base clause(not sure about that). As I later on created a real table for base, then it worked with no problem. Please take note, it might be only a problem for me, because the datas I had to recursive are about 200 million rows. And the result is around 290 millions.

    Hope, I didnt create missunderstanding to you all.

    Regards,
    Ratna

Posting Permissions

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