Connect by syntax

Source: Internet
Author: User
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;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.