Oracle Connect by prior... start with - running query on all children
Short version of question: is there any way to have the "Connect
prior..... start with" statement in Oracle to start with the first "child"
in the table and then run the query for all the remaining children as
well? I know how to use the statement for 1 child and get its ancestry,
but I need the code to run for lots of different children in the table.
Long version:
Let's suppose I have this code which returns a Component and Component's
Color from table of components and the Component's parent and that is
classified as a "Sample" and its color where component's parent ID is the
ID of the parent for the component.
Components table:
Component Component_ID Component_Parent_ID
Component_Color Table:
Component Component_Color
SELECT Component,
FROM Components
INNER JOIN Component_Color ON (Components.Component =
Component_Color.Component)
WHERE Component_Label = 'Sample'
connect by prior Component_Header.Component_Parent_ID =
Component_Header.Component_ID
start with Component.Component_ID = "2000";
Table Component has a lot of different components and I want the query to
return not only the component with ID 2000 and its "sample" parent, but do
this for all the children in the table "Components".
I can see how this can be done with a loop, but I cannot find what type of
loop I can use for Oracle. Sorry, just starting.
Thank you!
No comments:
Post a Comment