× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Hi,

Kent Milligan recently wrote an article about OLAP-Ranking Functions and
Recursive Common Table Expressions:

V5R4 SQL Packs a Punch - Kent Milligan
http://www-03.ibm.com/servers/eserver/iseries/db2/pdf/rcte_olap.pdf

Mit freundlichen Grüßen
i.A. Birgitta Hauser

LUNZER + PARTNER GMBH
Consulting | Software | Service
Carl-Zeiss-Straße 1
63755 Alzenau

Tel:       + 49 6023 951-255
Fax:       + 49 6023 951-111
Internet.  www.lp-gmbh.com
           www.rpg-schulung.de
--------------------------------------------------------
Handelsregister: Aschaffenburg HRB 4720
Gerichtsstand und Erfüllungsort Alzenau
Ust-IdNr.: DE132093146
Geschäftsführer: Rudolf Gerbert
---------------------------------------------------------


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Chris Payne
Sent: Thursday, September 28, 2006 17:14
To: Midrange Systems Technical Discussion
Subject: RE: SQL recursion

Is there a good place to read about recursive SQL?

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Thursday, September 28, 2006 10:50 AM
To: 'Midrange Systems Technical Discussion'
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'



As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.