Oracle "CONNECT by" usage

Oracle "CONNECT by" is a hierarchical query clause that is typically used for queries of tree or hierarchical result sets. Its syntax is:

[ START WITHcondition ]

CONNECTBY[ NOCYCLE ] condition

Description
1. Start with: Tells the system which node to use as the root node to find and construct the result set, which is the highest node in the return record.
2. A ORA-01436 error is returned when there is a situation in the hierarchical query where the upper and lower layers are mutually parent-child nodes. At this point, you need to add the Nocycle keyword after connect by. At the same time, the connect_by_iscycle pseudo-column can be used to locate the specific nodes that have mutual parent-child loops. Connect_by_iscycle must be used in conjunction with the keyword nocycle.

[Example 1]

Create a departmental table with three fields that correspond to the Department ID, department name, and parent department ID, respectively

DEPID DEPNAME                                                                           UPPERDEPID

----------- -------------------------------------------------------------------------------- -----------

0 General Deparment                                                               

1 Development                                                                                0

2 qa                                                                                              0

3 Server Development                                                                         1

4 Client Development                                                                         1

5 ta                                                                                              2

6 Porject QA                                                                                 2

Now I'm going to implement the tree query results based on "CONNECT by."

SQL> SELECTRPAD( ‘ ‘, 2*(LEVEL-1), ‘-‘) || DEPNAME "DEPNAME",

CONNECT_BY_ROOT DEPNAME "ROOT",

CONNECT_BY_ISLEAF "ISLEAF",

LEVEL,

SYS_CONNECT_BY_PATH(DEPNAME, ‘/‘) "PATH"

FROMDEP

START WITHUPPERDEPID ISNULL

CONNECTBY PRIORDEPID = UPPERDEPID;

DEPNAME                        ROOT                    ISLEAF      LEVELPATH

------------------------------ ------------------- ---------- ---------- --------------------------------------------------------------------------------

General Deparment              General Deparment            0          1 /General Deparment

-Development                  General Deparment            0          2 /General Deparment/Development

---Server Development         General Deparment            1          3 /General Deparment/Development/Server Development

---Client Development         General Deparment            1          3 /General Deparment/Development/Client Development

-QA                           General Deparment            0          2 /General Deparment/QA

---TA                         General Deparment            1          3 /General Deparment/QA/TA

---Porject QA                 General Deparment            1          3 /General Deparment/QA/Porject QA

Description
1. Connect_by_root returns the topmost node of the current node
2. Connect_by_isleaf to determine if the leaf node is not a leaf node if there are child nodes underneath the node
3. Level pseudo-column indicates node depth
4. The Sys_connect_by_path function displays a detailed path, separated by "/"

Summarize:

1. Check sub-categories according to tree structure

select* from xxxx where levelpermission = 0 andvalidflag = 1

start withid = ‘20130718091552GRP60000001‘

connectby priorid=PARENTID

2. Querying the parent class based on the attribute structure

select* from xxxx where levelpermission = 0 andvalidflag = 1

start withid = ‘40288181455dbe1701455ee92b9d001d‘

connectby priorPARENTID=id

2. Querying the parent class based on the attribute structure

Partial reprint: http://www.cnblogs.com/lettoo/archive/2010/08/03/1791239.html

Oracle "CONNECT by" usage