A connect by query on all children

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.

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.

This is my schema:

Components table:
Component        Component_ID      Component_Parent_ID      Component_Label

Component_Color Table:
Component      Component_Color

Note: this is example code, not the one I am actually using, so if you silly errors, I am very sorry.

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.

What version of Oracle are you using? Version 11.2g supports recursive CTEs which can also be use for this. — Gordon Linoff, Aug 26 '13 at 11:56
Your "2000" is in double quotes which implies this is a column name. Is this correct or did you mean to use single quotes? The WHERE clause in a CONNECT BY is [evaluated _after_ the CONNECT BY](http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries003.htm#sthref2221); did you know this and is it what you want? — Ben, Aug 26 '13 at 11:57
@Ben: as the column is an "ID" it's probably not even a character column but should be used as a numeric value without any quotes. — , Aug 26 '13 at 11:58
It sounds as though you want to end up with a unique set of hierarchies, i.e. always start at the root. You _have_ to calculate what the root is first in order to do this... or your table might be set up in a manner which enables you to do this... lowest ID for instance? — Ben, Aug 26 '13 at 12:00
Gordon - Version: Version 3.2.20.09 and I do not think I can use CTEs, but if you know any other method, please let me know. Ben - Yes, sorry - I just wrote it up as an example and did not double check it. Thank you. I will fix. — RebeccaK375, Aug 26 '13 at 12:00
Too true @a_horse_with_no_name. user2707197, you're not using Oracle version 3 :-). `select * from v$version`. — Ben, Aug 26 '13 at 12:02
There is no Oracle version 3.2.20.09. Wild guess: what happens when you leave out the `start with` completely? As all rows in the component table are "root" nodes and the child hierarchy is in component_header this might work — , Aug 26 '13 at 12:02
Hi, that worked. Thank you. Wow, cannot believe I did not try that. Post the answer and i'll select it. Thank you thank you thank you — RebeccaK375, Aug 26 '13 at 12:04

Benoit · Accepted Answer · 2013-08-26T12:15:41.727

Avoid using WHERE and ORDER BY clauses in hierachical queries, because they run after the hierarchical processing. Here I think you should use START WITH component_label = 'Sample'.

 SELECT level, components.component_id, components.component_label, component_color.component_color
   FROM compenents JOIN component_color ON components.component = component_color.component
  START WITH components.component_label = 'Sample'
CONNECT BY PRIOR components.component_id = components.component_parent_id

A connect by query on all children

1 Answers1