CONNECT BY

The CONNECT BY clause allows a table to be joined with itself in order to process hierarchical data. The CONNECT BY sub-clause of the FROM clause performs iterative evaluation to handle such structures.

See also WITH and the "Common Table Expressions" guide.

Syntax

SELECT col_list [, level_expr]  
  FROM data_source 
    START WITH predicate
    CONNECT BY [PRIOR] col1_ident = [PRIOR] col2_ident  
           [ , [PRIOR] col3_ident = [PRIOR] col4_ident] 
           ...
  ...
column_list

In general, the usage of this clause follows the rules for the projection clause of a SELECT statement.

Parameters and Clauses

level_expr (optional): A pseudo column (alias LEVEL) that indicates the current hierarchy level (level 1 represents the top level of the hierarchy). The projection clause of the query can use LEVEL as a column.

---

data_source: The data source (table, view).

---

predicate: An expression that selects the first "level" of the hierarchy (i.e., the root component in a parts explosion).

---

col[1,2...N]_ident: An expression referring to a column in data_source. A column at the "current" level should refer to a column at the parent level of the table.

Usage Notes

• PRIOR indicates that the value should be taken from the parent level. Example: ... CONNECT BY over_category = PRIOR category .... In this example, the over_category of the current level must match the category of the parent level.

• Multiple hierarchical expressions are allowed. Example: ... CONNECT BY y = PRIOR x AND b = PRIOR a ....

• Each CONNECT BY expression must include exactly one occurrence of the PRIOR keyword. PRIOR can appear on either side of the = operator:

  • CONNECT BY col1_ident = PRIOR col2_ident or
  • CONNECT BY PRIOR col1_ident = col2_ident

• A CONNECT BY clause always joins a table with itself, never with another table.

• Level expressions can appear in any order (regardless of their position after col_list in the syntax diagram).

• A query using CONNECT BY can also include filters in a WHERE clause and/or JOINs (in either the FROM or WHERE clause). The evaluation order is as follows:

  • JOINs (regardless of whether specified in WHERE or FROM)
  • CONNECT BY
  • Other filters

For example, filters in a WHERE clause are evaluated after the CONNECT BY clause.

• The SYS_CONNECT_BY_PATH function used with CONNECT BY returns a string representing the path from the root to the current element.
• The CONNECT_BY_ROOT operator allows the current level to access information from the root level of the hierarchy, even if the root is not the immediate parent of the current level.

Important

Since the CONNECT BY clause can perform as many iterations as needed to process the data, a poorly constructed query can result in an infinite loop. The query will continue executing until one of the following occurs:

• the query finishes successfully
• the user cancels the query

See also WITH.

Example 1

SELECT ord, parent_ord, col1
      FROM  (SELECT * FROM (SELECT 'a' AS ord, 'g1' AS grp, '10' AS col1, NULL AS parent_ord
             UNION ALL 
             SELECT 'b' AS ord, 'g2' AS grp, '20' AS col1, 'a' AS parent_ord
             UNION ALL
             SELECT 'c' AS ord, 'g1' AS grp, '30' AS col1, 'b' AS parent_ord
             UNION ALL
             SELECT 'd' AS ord, 'g2' AS grp, '40' AS col1, 'a' AS parent_ord
             UNION ALL
             SELECT 'e' AS ord, 'g1' AS grp, '50' AS col1, 'a' AS parent_ord
             UNION ALL
             SELECT 'f' AS ord, 'g2' AS grp, '40' AS col1, 'd' AS parent_ord
             UNION ALL
             SELECT 'g' AS ord, 'g1' AS grp, '50' AS col1, 'd' AS parent_ord
             ))
    START WITH ord = 'a'
    CONNECT BY
      parent_ord = PRIOR ord
  ORDER BY ord;

+-----+------------+------+ 
| ORD | PARENT_ORD | COL1 | 
+-----+------------+------+
| a   | NULL       | 10   | 
| b   | a          | 20   | 
| c   | b          | 30   | 
| d   | a          | 40   | 
| e   | a          | 50   | 
| f   | d          | 40   | 
| g   | d          | 50   | 
+-----+------------+------+

Example 2 | 'SYS_CONNECT_BY_PATH' Function

SELECT SYS_CONNECT_BY_PATH(ord, '>> ') AS path, ord, parent_ord, col1
  FROM  (SELECT * FROM (SELECT 'a' AS ord, 'g1' AS grp, '10' AS col1,  NULL AS parent_ord
         UNION ALL 
         SELECT 'b' AS ord, 'g2' AS grp, '20' AS col1,  'a' AS parent_ord
         UNION ALL
         SELECT 'c' AS ord, 'g1' AS grp, '30' AS col1, 'b' AS parent_ord
         UNION ALL
         SELECT 'd' AS ord, 'g2' AS grp, '40' AS col1,  'a' AS parent_ord
         UNION ALL
         SELECT 'e' AS ord, 'g1' AS grp, '50' AS col1,  'a' AS parent_ord
         UNION ALL
         SELECT 'f' AS ord, 'g2' AS grp, '40' AS col1,  'd' AS parent_ord
         UNION ALL
         SELECT 'g' AS ord, 'g1' AS grp, '50' AS col1,  'd' AS parent_ord
         ))
    START WITH ord = 'a'
    CONNECT BY
      parent_ord = PRIOR ord
  ORDER BY ord;

+--------------+-----+------------+------+ 
| PATH         | ORD | PARENT_ORD | COL1 | 
+--------------+-----+------------+------+
| >> a         | a   | NULL       | 10   | 
| >> a>> b     | b   | a          | 20   | 
| >> a>> b>> c | c   | b          | 30   | 
| >> a>> d     | d   | a          | 40   | 
| >> a>> e     | e   | a          | 50   | 
| >> a>> d>> f | f   | d          | 40   | 
| >> a>> d>> g | g   | d          | 50   | 
+--------------+-----+------------+------+

Example 3 | 'CONNECT_BY_ROOT' Function

Display the root of the hierarchy for each element:

SELECT CONNECT_BY_ROOT ord AS root, ord, parent_ord, col1
  FROM   (SELECT * FROM (SELECT 'a' AS ord, 'g1' AS grp, '10' AS col1,  NULL AS parent_ord
          UNION ALL 
          SELECT 'b' AS ord, 'g2' AS grp, '20' AS col1,  'a' AS parent_ord 
          UNION ALL
          SELECT 'c' AS ord, 'g1' AS grp, '30' AS col1, 'b' AS parent_ord
          UNION ALL
          SELECT 'd' AS ord, 'g2' AS grp, '40' AS col1,  'a' AS parent_ord
          UNION ALL
          SELECT 'e' AS ord, 'g1' AS grp, '50' AS col1,  'a' AS parent_ord
          UNION ALL
          SELECT 'f' AS ord, 'g2' AS grp, '40' AS col1,  'd' AS parent_ord
          UNION ALL
          SELECT 'g' AS ord, 'g1' AS grp, '50' AS col1,  'd' AS parent_ord
          ))
    START WITH ord = 'a'
    CONNECT BY 
      parent_ord = PRIOR ord
  ORDER BY ord;

+------+-----+------------+------+ 
| ROOT | ORD | PARENT_ORD | COL1 | 
+------+-----+------------+------+
| a    | a   | NULL       | 10   | 
| a    | b   | a          | 20   | 
| a    | c   | b          | 30   | 
| a    | d   | a          | 40   | 
| a    | e   | a          | 50   | 
| a    | f   | d          | 40   | 
| a    | g   | d          | 50   | 
+------+-----+------------+------+

Example 4 | Multiple Hierarchy Memberships

SELECT SYS_CONNECT_BY_PATH(ord, '>>') AS path, ord, parent_ord, col1
  FROM   (SELECT * FROM (SELECT 'a' AS ord, 'g1' AS grp, '10' AS col1,  NULL AS parent_ord
          UNION ALL 
          SELECT 'b' AS ord, 'g2' AS grp, '20' AS col1,  'a' AS parent_ord 
          UNION ALL
          SELECT 'c' AS ord, 'g1' AS grp, '30' AS col1, 'b' AS parent_ord
          UNION ALL
          SELECT 'd' AS ord, 'g2' AS grp, '40' AS col1,  'a' AS parent_ord
          UNION ALL
          SELECT 'e' AS ord, 'g1' AS grp, '50' AS col1,  'a' AS parent_ord
          UNION ALL
          SELECT 'f' AS ord, 'g2' AS grp, '40' AS col1,  'd' AS parent_ord
          UNION ALL
          SELECT 'g' AS ord, 'g1' AS grp, '50' AS col1,  'd' AS parent_ord
          UNION ALL
          SELECT 'g' AS ord, 'g2' AS grp, '50' AS col1,  'b' AS parent_ord
          ))
    START WITH ord = 'a'
    CONNECT BY 
      parent_ord = PRIOR ord
  ORDER BY ord;

+-----------+-----+------------+------+ 
| PATH      | ORD | PARENT_ORD | COL1 | 
+-----------+-----+------------+------+
| >>a       | a   | NULL       | 10   | 
| >>a>>b    | b   | a          | 20   | 
| >>a>>b>>c | c   | b          | 30   | 
| >>a>>d    | d   | a          | 40   | 
| >>a>>e    | e   | a          | 50   | 
| >>a>>d>>f | f   | d          | 40   | 
| >>a>>d>>g | g   | d          | 50   |   -- Element 'g' belongs to 2 hierarchies.
| >>a>>b>>g | g   | b          | 50   |   -- Element 'g' belongs to 2 hierarchies.
+-----------+-----+------------+------+