CONNECT_BY_ROOT unary operator

expression

An expression that does not contain a NEXT VALUE expression, an hierarchical query construct (such as the LEVEL pseudocolumn), the SYS_CONNECT_BY_PATH function, or an OLAP function (SQLSTATE 428H4).

The result type of the operator is the result type of the expression.

Rules:

• A CONNECT_BY_ROOT operator cannot be specified in the START WITH clause or the CONNECT BY clause of a hierarchical query (SQLSTATE 428H4).
• A CONNECT_BY_ROOT operator cannot be specified as an argument to the SYS_CONNECT_BY_PATH function (SQLSTATE 428H4).

Notes:

• A CONNECT_BY_ROOT operator has a higher precedence than any infix operator. Therefore, to pass an expression with infix operators (such as + or ||) as an argument, parentheses must be used. For example:

     CONNECT_BY_ROOT FIRSTNME || LASTNAME

  returns the FIRSTNME value of the root ancestor row concatenated with the LASTNAME value of the actual row in the hierarchy, because this expression is equivalent to:

     (CONNECT_BY_ROOT FIRSTNME) || LASTNAME

  rather than:

     CONNECT_BY_ROOT (FIRSTNME || LASTNAME)

Example:

• Return the hierarchy of departments and their root departments in the DEPARTMENT table.

     SELECT CONNECT_BY_ROOT DEPTNAME AS ROOT, DEPTNAME
       FROM DEPARTMENT START WITH DEPTNO IN ('B01','C01','D01','E01')
       CONNECT BY PRIOR DEPTNO = ADMRDEPT

  This query returns:

  ROOT               DEPTNAME
  ------------------ -----------------------
  PLANNING           PLANNING
  INFORMATION CENTER INFORMATION CENTER
  DEVELOPMENT CENTER DEVELOPMENT CENTER
  DEVELOPMENT CENTER MANUFACTURING SYSTEMS
  DEVELOPMENT CENTER ADMINISTRATION SYSTEMS
  SUPPORT SERVICES   SUPPORT SERVICES
  SUPPORT SERVICES   OPERATIONS
  SUPPORT SERVICES   SOFTWARE SUPPORT
  SUPPORT SERVICES   BRANCH OFFICE F2
  SUPPORT SERVICES   BRANCH OFFICE G2
  SUPPORT SERVICES   BRANCH OFFICE H2
  SUPPORT SERVICES   BRANCH OFFICE I2
  SUPPORT SERVICES   BRANCH OFFICE J2