RE: SQL recursion -- MIDRANGE-L

How do you get this type of statement to work?

I tried (a modified version of) it on our BOM file just now, and got an 
SQL error:

[SQL0346] Recursion not allowed in common table expressions.
SQL State: 42836
Error Code: +346

Running QuantumDB connected to a V5R3M0 system, using this statement:

with exploded(parent, child, qty, lvl) as
        (select pdmnbr55, itmref55, boiqt1551
                from pdmdta.pdbl55pf where itmref55 <> ''
        union all
        select pdmnbr55, itmref55, boiqt155, lvl+1
                from pdmdta.pdbl55pf, exploded)
select * from exploded
order by pdmnbr55;

Thanks,

Peter Colpaert
Application Developer
PLI - IT - Kontich, Belgium
-----
Yoda of Borg are we.  Futile is resistance, assimilated will you be.
-----



"Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
28/09/2006 16:50
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
cc

Subject
RE: SQL recursion






Yeah, this is a pretty snazzy bit of sleight of hand.  The SQL committee
added CTEs to the language specifically to handle recursion.  And it works
very well for a simple BOM.  Of course, it gets a little hairy when you 
have
things like batch quantities and co- and by-products, so it's not for
everyone.  But as long as your rules are simple enough that you don't get
lost in a maze of CASE statements, a good SQL recursion is probably going 
to
greatly outperform native I/O, especially in a batch processing 
environment.

I'd be interested to know how well that same statement performs against
native I/O for exploding a single BOM.  I bet there's still a point where
native outperforms SQL, probably when you're dealing with less than 100
records.  But I could be wrong, especially if i5/OS manages to cache some 
of
its index information.

Joe

From: rob@xxxxxxxxx

Yeah, I remember that "back in the day" stuff.  The problem is that many
people, once told something, never change.  There are still people that
believe that.  There are still people leery of each LF they add because

of

performance considerations  (granted I'm not advocating 100LF's on 1 PF
but let's not swing to the other extreme).  And a lot of other items

that

were true "back in the day".

And a coworker here just wrote a V5R4 recursive SQL statement to explode
MBM that blew the socks off of traditional RPG logic.

with Exploded(Parent, Child, Qty, LVL, TopParent) as
  (select BPROD, BCHLD, BQREQ, 1, BPROD
    from MBM
  union all
  select BPROD, BCHLD, BQREQ*Exploded.Qty, LVL+1, TopParent
    from MBM, Exploded
      where BPROD=Child)
  select * from Exploded
 order by TopParent

For this to work in our database, the QAQQINI file must have setting
IGNORE_DERIVED_INDEX='*YES'