devxlogo

Use Of ‘Connect By’ clause for Hierarchal Query

Use Of ‘Connect By’ clause for Hierarchal Query

The Connect By clause is useful for tables which have 2 or more columns with parent-child relationships. While displaying rows, we can use ‘LEVEL’ pseudo column to indent the results as hierarchy.
Consider the following example of a community table:

 Community_IdParent_IdCommunity_Nametmr>Select * from Communities;COMMUNITY_ID  PARENT_ID COMMUNITY_NAME------------ ---------- -------------------------           1          0 TV Entertainment           2          1 NBC           3          1 ABC           4          1 FOX           5          2 Sports           6          2 Movies           7          3 Talk Show           8          3 Documentary           9          7 Educational          10          7 Fun10 rows selected./* Find all children, all leaf lets From Root*/ SELECT LPAD(' ',3*(LEVEL-1)) || substr(Community_Name, 1, 30) Community ,         Community_id, Parent_Id FROM   Communities CONNECT BY PRIOR Community_id = Parent_Id START WITH Parent_id= 0Results:COMMUNITY                           COMMUNITY_ID  PARENT_ID----------------------------------- ------------ ----------TV Entertainment                               1          0   NBC                                         2          1      Sports                                   5          2      Movies                                   6          2   ABC                                         3          1      Talk Show                                7          3         Educational                           9          7         Fun                                  10          7      Documentary                              8          3   FOX                                         4          110 rows selected./* Find immediate children For Parent_Id = 1 : Just go 1 level Down  */ SELECT LPAD(' ',3*(LEVEL-1)) || substr(Community_Name, 1, 30) Community ,         Community_id, Parent_Id FROM   Communities CONNECT BY Community_Id = PRIOR  Parent_Id START WITH Parent_Id= 1Results:COMMUNITY                           COMMUNITY_ID  PARENT_ID----------------------------------- ------------ ----------NBC                                            2          1   TV Entertainment                            1          0ABC                                            3          1   TV Entertainment                            1          0FOX                                            4          1   TV Entertainment                            1          06 rows selected./* Start At Node Community_Id = 7: Traverse up all the way till root */ SELECT LPAD(' ',3*(LEVEL-1)) || substr(Community_Name, 1, 30) Community ,         Community_id, Parent_Id FROM   Communities CONNECT BY Community_id = PRIOR  Parent_Id START WITH Community_id = 7Results:COMMUNITY                           COMMUNITY_ID  PARENT_ID----------------------------------- ------------ ----------Talk Show                                      7          3   ABC                                         3          1      TV Entertainment                         1          0/* Find all children, upto some LEVEL */ SELECT LPAD(' ',3*(LEVEL-1)) || substr(Community_Name, 1, 30) Community ,         Community_id, Parent_Id FROM   Communities Where  LEVEL 

size=3>
Results:

 COMMUNITY                           COMMUNITY_ID  PARENT_ID----------------------------------- ------------ ----------TV Entertainment                               1          0   NBC                                         2          1   ABC                                         3          1   FOX                                         4          1

size=3>

devx-admin

Share the Post: