Re: Recursion and SQL

For good examples of recursion in DB2 UDB for LUW, get Chamberlin's book
on DB2. The section on recursion walks you through the entire process.
Don is one of the original creators of the SQL language.

And recursion in SQL is not supported on V5R3 either.


On Wed, 2005-03-30 at 09:47, Smith, Dave wrote:
> I am looking for a good example of breaking down a table that forms a
> parent child relationship of unknown levels (Tree structure).
> 
>  
> 
> PARENT           CHILD
> 
>                         A
> 
> A                      B          
> 
> A                      C          
> 
> A                      D                      
> 
> B                      E                      
> 
> B                      F                      
> 
> E                      G                      
> 
> E                      J                                   
> 
> G                      K                      
> 
> H                      L          
> 
>  
> 
> I saw some good examples of recursive SQL on the web but apparently
> that's not allowed on the iSeries V5R2.   I believe in won't be allowed
> under V5R3 but I can not find where I read that.  
> 
>  
> 
> I get the following message when trying a recursive SQL statement:
> Message: [SQL0346] Recursion not allowed for common table expressions.
> 
>  
> 
> This is the result I'm looking for:
> 
>  
> 
> LEVEL              PARENT           CHILD  
> 
> 1                      A                      B          
> 
> 2                      B                      E          
> 
> 3                      E                      G          
> 
> 4                      G                      K          
> 
> 3                      E                      J
> 
> 2                      B                      F
> 
> 
> 1                      A                      C
> 
> 1                      A                      D
> 
> 1                      H                      L
> 
>  
> 
> I can then easily show this as:
> 
> LEVEL  1          2          3          4                      
> 
>             A          B
> 
>                         B          E
> 
>                                     E          G
> 
>                                                 G          K
> 
>                                     E          J
> 
>                         B          F
> 
>             A          C
> 
>             A          D
> 
>             H          L                      
> 
>  
> 
> I want to do this for a warehousing application to show who feeds who.
> I was able to union a bunch of SQL statements together to achieve the
> desired results but it's ugly and does not account for unlimited levels.
> I can do it using RPG or using a combination of RPG and embedded SQL.
> Is there a simple SQL only solution that I'm missing?
> 
>  
> 
> David Smith
> 
> IT Consultant
> 
>  
-- 
"Bigamy is having one wife too many. Monogamy is the same."
-- Oscar Wilde