The CONNECT_BY_ROOT unary operator is for use only in hierarchical queries. For every row in the hierarchy, this operator returns the expression for the root ancestor of the row.
The result type of the operator is the result type of the expression.
The following rules apply to the CONNECT_BY_ROOT operator:
CONNECT_BY_ROOT FIRSTNME || LASTNAME
That
expression is equivalent to the first one in the following list but
not the second one: (CONNECT_BY_ROOT FIRSTNME) || LASTNAME
CONNECT_BY_ROOT (FIRSTNME || LASTNAME)
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 the following results: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