× 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.




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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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.