I believe you can find many examples on the Internet using the connect by syntax. To describe my problems.
The data in the table (fnd_flex_value) is as follows:
Type_id parent_value child_value
139048 2701 270101
139048 270101 27010101
139058 2701 270101
139058 270101 27010101
Execute the following statement:
Select * From fnd_flex_value FFV
Where type_id = 139048
Start with child_value = 270101
Connect by prior child_value = FFV. parent_value;
The execution result is:
Type_id parent_value child_value
139048 2701 270101
139048 270101 27010101
139048 270101 27010101
The last record is repeated twice. We only want the first two results. How can we get the third record? After research, I personally think it may be because of the following reasons (if it is incorrect, please correct it ):
During the execution of this SQL statement, connect by and G are executed before the where condition is executed.
The result of the loop is:
The first cycle (starting from start with child_value = 2701) to find data:
(1.1) 139048 2701 270101
(1.2) 139058 2701 270101
The second cycle (starting from (1.1:
(2.1) 139048 270101 27010101
(2.2) 139058 270101 27010101
The third cycle (starting from (1.2:
(3.1) 139048 270101 27010101
(3.2) 139058 270101 27010101
Exit the loop and run where type_id = 139048.
Three records are obtained.
If you want to get the correct result, change the SQL statement
Select * from
(Select * From fnd_flex_value where type_id = 139048) FFV
Start with child_value = 270101
Connect by prior child_value = FFV. parent_value;