Here are two examples that we use based on our custom bill-of-material
table (named: BOM). The first one is a "top-down" query that show the
components for a particular product. The second one is the reverse, a
"bottom-up" query that starts with a particular component part, and
travels up to the top-level products that use it.

SELECT child_part_nbr
FROM BOM
WHERE STATUS_CODE != 'O'
START WITH PARENT_PART_NBR = '&part_nbr'
CONNECT BY PRIOR CHILD_PART_NBR = PARENT_PART_NBR;

SELECT parent_part_nbr
FROM BOM
WHERE STATUS_CODE != 'O'
START WITH CHILD_PART_NBR = '&v_part_nbr'
CONNECT BY CHILD_PART_NBR = PRIOR PARENT_PART_NBR;

-----Original Message-----
From: Tushar Dave
[mailto:oracledba-ezmlmshield-x51691238.[Email address protected]
Sent: Wednesday, October 31, 2007 3:04 AM
To: LazyDBA Discussion
Subject: CONNECT BY in Oracle for a recursive query

Has anyone used CONNECT BY in Oracle for a recursive query? If so, can
you
give me an example or let me know how it works.



Tushar Dave

Intrack Inc





---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



THE INFORMATION CONTAINED IN THIS E-MAIL MESSAGE AND ANY ATTACHMENTS SENT FROM GENTEX CORPORATION IS GENTEX CONFIDENTIAL INFORMATION INTENDED ONLY FOR THE PERSONAL USE OF THE INDIVIDUAL OR ENTITY NAMED ABOVE. If you are not the intended recipient, you are hereby notified that any review, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail, and delete this e-mail message and any attachments from your computer.



Oracle LazyDBA home page