Re: AW: Recursive SQL question

["sjl" <sjl_abc@xxxxxxxxxxx>]

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


Thanks, Birgitta -

Here is the revised SQL statement that produces the result that I desired:


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

         )

   Search Depth First by IXKITL,IXLITM Set Sort1

   SELECT

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

     FROM BOM

    Order by Sort1


Which produces the following output:

LEVEL  IXKITL                IXCPNT  IXLITM

     1    PARENTITEM            10     12-100-006
     2    12-100-006                 21    10-800-090-NEW
     2    12-100-006                 30    10-800-114-NEW
     1    PARENTITEM             21    12-100-N68
     2    12-100-N68                11    40-700-005
     3    40-700-005                10    40-700-003-OBS
     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
     3    40-700-005                30    40-100-005-NEW
     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
     1    PARENTITEM            30    11-100-297
     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