Home » SQL & PL/SQL » SQL & PL/SQL » Hierarcial retrieval - Connect By, Prior Not working
Hierarcial retrieval - Connect By, Prior Not working [message #209155] Wed, 13 December 2006 09:20 Go to next message
arunsenthild
Messages: 17
Registered: October 2006
Location: Bangalore
Junior Member

Hi all,
I've two tables
BOM_HDR and BOM_DTL

BOM_HDR

BOM_NO BOM_DATE BOM_CUST
BOM/1 12/12/06 C0015
BOM/2 12/12/06 C0016
BOM/3 11/12/06 C0017
BOM/4 11/12/06 C0018

BOM_DTL

BOM_NO BOM_PROD BOM_QTY BOM_SUB_NO
BOM/1 P1001 10 BOM/2
BOM/1 P1002 20 NULL
BOM/1 P1003 30 NULL
BOM/1 P1004 40 NULL
BOM/2 P1005 10 NULL
BOM/2 P1006 20 BOM/3
BOM/2 P1007 30 NULL
BOM/3 P1008 40 NULL
BOM/3 P1009 40 NULL
BOM/4 P1010 50 NULL

The thing here i need is, if i select BOM_NO='BOM/1' in the where clause then the result should be as follows.

output

BOM_NO BOM_PROD
BOM/1 P1002
BOM/1 P1003
BOM/1 P1004
BOM/2 P1005
BOM/2 P1007
BOM/3 P1008
BOM/3 P1009

Those which has SUB BOMs it should not come in the list but the sub boms it has should come. P1001 and P1006 should not come also the BOM_NO may extend to no of SUB_BOMS.

I tried this using START WITH-CONNECT BY and PRIOR but i cant get the result.
Am able to make this in PL/SQL but in SQL i cant make it. Am really frustrated with this issue.
Is Any prodigy over there to solve this issue!!
Thank ya in Advance.

Arun.
Re: Hierarcial retrieval - Connect By, Prior Not working [message #209169 is a reply to message #209155] Wed, 13 December 2006 10:21 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Try:

SELECT OUTPUT, BOM_PROD FROM
(SELECT LEVEL AS LV, LPAD(' ', 2*level-1)||bom_no as output, bom_prod, bom_sub_no, bom_no 
   FROM BOM
   START WITH BOM_NO = 'BOM/1'
   CONNECT BY PRIOR BOM_sub_NO = BOM_NO)
WHERE BOM_SUB_NO IS NULL
ORDER BY LV
Previous Topic: column value as column name
Next Topic: Optimizing query with alpanumeric range condition
Goto Forum:
  


Current Time: Mon Dec 05 21:30:12 CST 2016

Total time taken to generate the page: 0.08563 seconds