AW: Recursive SQL question

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