What does connect by Prior clause will do?
To display hierarchical data. We can use this. A condition that identifies the relationship between parent rows and child rows of the hierarchy.
Syntax:
[START WITH initial_condition]
CONNECT BY [nocycle] PRIOR recurse_condition
[ORDER SIBLINGS BY order_by_clause]
Start with: Specify the value for Start with so it will consider that as a Root Row. If start with clause is not specified all rows are considered root rows.
Connect By: Condition that identifies the relationship between parent and child rows of the hierarchy.
Prior: This keyword will evaluate parent row of the current row.
For example take some records like R_RECORD, R_RECORD2, R_RECORD3 and R_FLAG
R_FLAG record's child record is R_RECORD.
R_RECORD record's child record is R_RECORD2.
R_RECORD2 record's child record is R_RECORD3.
Select rownum, recname from psrecdefn
CONNECT BY RECNAME=PARENTRECNAME
START WITH PARENTRECNAME = 'R_FLAG';
Output:
ROWNUM RECNAME PARENTRECN
---------- --------------- --------------
1 R_RECORD R_FLAG
Only one row will be returned since you searching for the record R_FLAG without prior keyword.
So if you need parents Childs and that Childs child, till the end then use the prior keyword which is used to return all Childs child.
Select rownum, recname, parentrecname from psrecdefn
CONNECT BY PRIOR RECNAME=PARENTRECNAME
START WITH PARENTRECNAME = 'R_FLAG';
Output:
ROWNUM RECNAME PARENTRECNAM
---------- --------------- ---------------
1 R_RECORD R_FLAG
2 R_RECORD2 R_RECORD
3 R_RECORD3 R_RECORD2
1 R_RECORD R_FLAG
2 R_RECORD2 R_RECORD
3 R_RECORD3 R_RECORD2
So first R_FLAG table’s child R_RECORD will be returned then R_RECORD table’s child R_RECORD2 will be returned then R_RECORD2 table’s child R_RECORD3 will be retuned.
