AW: Recursive SQL question

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

Short answer is yes you have to add Search Depth First between the common
table expression and the final select statement.

Example (Sorry I did not rewrite your statement):
MySort is a temporary column which is used for ordering the result set and
can have any name.
To get the result in the sequence you wish, you need to specify this
temporary field in your final order by clause.

WITH MyCTE AS 
-- Initial Select Statement
              (Level, ItemNo, Descr)        
              ( SELECT   1, ItemNo, Descr
                   FROM  MyFile
                   WHERE ItemNo = 4711
-- Iteratation
               UNION ALL
                 SELECT   Level + 1,  F.ItemId, F.Descr
                   FROM  MyFile F Join MyCTE C On F.ItemNo = C.ItemNo)

     SEARCH DEPTH FIRST BY ItemNo SET MySort

-- Final Select Statement
SELECT   Level, ItemNo, Descr 
   FROM  MyCTE
   Order By MySort

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von sjl
Gesendet: Friday, 24. April 2009 02:14
An: midrange-l@xxxxxxxxxxxx
Betreff: Recursive SQL question

> From the JDE multilevel BOM Inquiry screen on PARENTITEM:


PARENTITEM

Level        Component

1     12-100-006
.2    10-800-090-NEW
.2    10-800-114-NEW
1     12-100-N68
.2    40-700-005
..3   40-700-003-OBS
...4  40-100-001-NEW
...4  40-100-002-NEW
...4  40-100-003-NEW
...4  40-100-004-NEW
...4  40-100-018-NEW
..3   40-100-005-NEW
.2    10-800-072-003
.2    10-500-105-007
.2    50-800-068-NEW
1     11-100-297
.2    10-500-095-002
.2    40-500-021-002
.2    40-500-008-001
.2    10-500-059-003
.2    40-800-026-002



I found an example of recursive SQL in the IBM V5R4 SQL reference.
When I use the following recursive SQL to explode the Bill of Materials for 
PARENTITEM:


WITH BOM

          (

            Level
          , IXKITL
          , IXLITM
          , IXQNTY
          , IXCPNT
          , IXEFFF
          , IXEFFT
          , IXTBM
          , IXBREV
          , IXCOBY
          , IXSBNT
          , IXMMCU
          )

     AS

        (SELECT
                  1
                , ROOT.IXKITL
                , ROOT.IXLITM
                , ROOT.IXQNTY
                , ROOT.IXCPNT
                , ROOT.IXEFFF
                , ROOT.IXEFFT
                , ROOT.IXTBM
                , ROOT.IXBREV
                , ROOT.IXCOBY
                , ROOT.IXSBNT
                , ROOT.IXMMCU

              FROM &FILELIB/F3002 ROOT

              WHERE ROOT.IXKITL =  &PARENT
                and ROOT.IXMMCU =  &MCU
                and ROOT.IXEFFF <= &ASOFDATE
                and ROOT.IXEFFT >= &ASOFDATE

          UNION ALL

           SELECT
                  PARENT.LEVEL + 1
                , CHILD.IXKITL
                , CHILD.IXLITM
                , CHILD.IXQNTY
                , CHILD.IXCPNT
                , CHILD.IXEFFF
                , CHILD.IXEFFT
                , CHILD.IXTBM
                , CHILD.IXBREV
                , CHILD.IXCOBY
                , CHILD.IXSBNT
                , CHILD.IXMMCU

              FROM BOM PARENT, &FILELIB/F3002 CHILD

              WHERE PARENT.IXLITM =  CHILD.IXKITL
                and  CHILD.IXMMCU =  &MCU
                and  CHILD.IXEFFF <= &ASOFDATE
                and  CHILD.IXEFFT >= &ASOFDATE

         )


   SELECT

           Level
         , IXKITL
         , IXCPNT
         , IXLITM
         , IXQNTY
         , IXEFFF
         , IXEFFT
         , IXTBM
         , IXBREV
         , IXCOBY
         , IXSBNT
         , IXMMCU

     FROM BOM


Produces the following output (trimmed down so as not to wrap):


LEVEL  IXKITL                 IXCPNT  IXLITM

       1   PARENTITEM              10  12-100-006
       1   PARENTITEM              21  12-100-N68
       1   PARENTITEM              30  11-100-297
       2   12-100-006                  21  10-800-090-NEW
       2   12-100-006                  30  10-800-114-NEW
       2   12-100-N68                  11  40-700-005
       2   12-100-N68                  40  10-800-072-003
       2   12-100-N68                  62  10-500-105-007
       2   12-100-N68                  80  50-800-068-NEW
       2   11-100-297                  14  10-500-095-002
       2   11-100-297                  23  40-500-021-002
       2   11-100-297                  34  40-500-008-001
       2   11-100-297                  90  10-500-059-003
       2   11-100-297                100  40-800-026-002
       3   40-700-005                 10  40-700-003-OBS
       3   40-700-005                 30  40-100-005-NEW
       4   40-700-003-OBS         10  40-100-001-NEW
       4   40-700-003-OBS         20  40-100-002-NEW
       4   40-700-003-OBS         30  40-100-003-NEW
       4   40-700-003-OBS         41  40-100-004-NEW
       4   40-700-003-OBS         50  40-100-018-NEW



My question:

The SQL query shows all 21 components, but is it possible to sequence the 
result set from the SQL query to match the sequence that is produced by the 
RPG program which uses native file access?

i.e., I want the components listed under their respective parent items, 
similar to the output from the inquiry screen shown above...

Regards,
sjl