Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Parent Child hierarchical query or Traversal query output

LPSMay 7 2020 — edited May 8 2020

I have MAST which has BOM and Material(component) relationship which needs to be traversed to STPO with BOM to identify the compenent (raw materials) and each component may be split into sub material which needs to be lookup for BOM and it goes on and i need to get the below output for BOM1 i.e 1001 BOM splits into 3 levels as output.Kindly let me know how to achieve this and i have tried with the below query not able to achieve.

Select * FROM (

select MAST.MATERIAL MAT,MAST.BOM PBOM,STPO.BOM CPOM,STPO.COMPONENT COMP from MAST ,  STPO

WHERE MAST.BOM = STPO.BOM  )

--START WITH MAT = '1001'

CONNECT BY PRIOR MAT = COMP;

 

MAST
BOM Material
BOM11001
BOM22001
BOM33001
BOM44001
BOM55001
BOM66001
BOM77001
BOM88001
BOM91002
BOM10

1009

  

"STPO"
BOMComponent
BOM11002
BOM11003
BOM11004
BOM11005
BOM22002
BOM22003
BOM22004
BOM22005
BOM22006
BOM91009
BOM91010
BOM101011
BOM10

1012

   

Output
PMATLEVELCOMPONENT
100111002
100111003
100111004
100111005
100221009
100221010
100931011
10093

1012

This post has been answered by mathguy on May 7 2020
Jump to Answer
Comments
Post Details
Added on May 7 2020
12 comments
895 views