Oracle – Quick Tip – CONNECT BY PRIOR & SYS_CONNECT_BY_PATH – Easily Construct Hierarchy Strings / Bread Crumbs / Navigation Paths

We have already seen “CONNECT BY LEVEL” on the tip about generating N rows dynamically in Oracle.

Today, let us look at CONNECT BY PRIOR which is used in Oracle to navigate hierarchical relationships represented within a single table.

Here is an imaginary organizational reporting chart with “Employee 1” at the very top.

  • Employee 1
    • Employee 1.1
    • Employee 1.2
      • Employee 1.2.1
        • Employee 1.2.1.1
        • Employee 1.2.1.2
        • Employee 1.2.1.3
      • Employee 1.2.2
    • Employee 1.3

A typical table structure would look as below (used SSMS diagramming 🙂 )

employee_self_reference_1

Let us over-simplify this for the purposes of illustration to

employee_self_reference_2-jpg

CONNECT BY PRIOR Magic

Here is a sample query:

WITH sample_data
AS
(
    SELECT TO_CHAR(NULL) AS Manager, 'Employee 1' AS Employee FROM DUAL
    UNION ALL
    SELECT 'Employee 1' AS Manager, 'Employee 1.1' AS Employee FROM DUAL
    UNION ALL
    SELECT 'Employee 1' AS Manager, 'Employee 1.2' AS Employee FROM DUAL
    UNION ALL
    SELECT 'Employee 1' AS Manager, 'Employee 1.3' AS Employee FROM DUAL
    UNION ALL
    SELECT 'Employee 1.2' AS Manager, 'Employee 1.2.1' AS Employee FROM DUAL
    UNION ALL
    SELECT 'Employee 1.2' AS Manager, 'Employee 1.2.2' AS Employee FROM DUAL
    UNION ALL
    SELECT 'Employee 1.2.1' AS Manager, 'Employee 1.2.1.1' AS Employee FROM DUAL
    UNION ALL
    SELECT 'Employee 1.2.1' AS Manager, 'Employee 1.2.1.2' AS Employee FROM DUAL
    UNION ALL
    SELECT 'Employee 1.2.1' AS Manager, 'Employee 1.2.1.3' AS Employee FROM DUAL
)
SELECT
    manager,
    employee,
    --Depending on the CONNECT BY LEVEL, prefix with respective number of spaces
    case when level = 1 then employee
            else lpad (' ', 3*(level-1)) || employee
        end nicer_view
FROM sample_data
    START WITH Manager IS NULL
    CONNECT BY Manager= PRIOR Employee

Sample Data:

The sample data by itself looks like so:

MANAGER EMPLOYEE
Employee 1
Employee 1 Employee 1.1
Employee 1 Employee 1.2
Employee 1 Employee 1.3
Employee 1.2 Employee 1.2.1
Employee 1.2 Employee 1.2.2
Employee 1.2.1 Employee 1.2.1.1
Employee 1.2.1 Employee 1.2.1.2
Employee 1.2.1 Employee 1.2.1.3

Query results:

Notice the last column

MANAGER EMPLOYEE NICER_VIEW
Employee 1 Employee 1
Employee 1 Employee 1.1    Employee 1.1
Employee 1 Employee 1.2    Employee 1.2
Employee 1.2 Employee 1.2.1       Employee 1.2.1
Employee 1.2.1 Employee 1.2.1.1          Employee 1.2.1.1
Employee 1.2.1 Employee 1.2.1.2          Employee 1.2.1.2
Employee 1.2.1 Employee 1.2.1.3          Employee 1.2.1.3
Employee 1.2 Employee 1.2.2       Employee 1.2.2
Employee 1 Employee 1.3    Employee 1.3

Results with spaces replaced with “>” for clarity

Notice the last column again!

MANAGER EMPLOYEE NICER_VIEW
Employee 1 Employee 1
Employee 1 Employee 1.1 >>>Employee 1.1
Employee 1 Employee 1.2 >>>Employee 1.2
Employee 1.2 Employee 1.2.1 >>>>>>Employee 1.2.1
Employee 1.2.1 Employee 1.2.1.1 >>>>>>>>>Employee 1.2.1.1
Employee 1.2.1 Employee 1.2.1.2 >>>>>>>>>Employee 1.2.1.2
Employee 1.2.1 Employee 1.2.1.3 >>>>>>>>>Employee 1.2.1.3
Employee 1.2 Employee 1.2.2 >>>>>>Employee 1.2.2
Employee 1 Employee 1.3 >>>Employee 1.3

Let us get fancier! SYS_CONNECT_BY_PATH magic:

Let us say we want something like this (notice the last column).

MANAGER EMPLOYEE NICER_VIEW EVEN_NICER_VIEW
Employee 1 Employee 1 ->Employee 1
Employee 1 Employee 1.1    Employee 1.1 ->Employee 1->Employee 1.1
Employee 1 Employee 1.2    Employee 1.2 ->Employee 1->Employee 1.2
Employee 1.2 Employee 1.2.1       Employee 1.2.1 ->Employee 1->Employee 1.2->Employee 1.2.1
Employee 1.2.1 Employee 1.2.1.1          Employee 1.2.1.1 ->Employee 1->Employee 1.2->Employee 1.2.1->Employee 1.2.1.1
Employee 1.2.1 Employee 1.2.1.2          Employee 1.2.1.2 ->Employee 1->Employee 1.2->Employee 1.2.1->Employee 1.2.1.2
Employee 1.2.1 Employee 1.2.1.3          Employee 1.2.1.3 ->Employee 1->Employee 1.2->Employee 1.2.1->Employee 1.2.1.3
Employee 1.2 Employee 1.2.2       Employee 1.2.2 ->Employee 1->Employee 1.2->Employee 1.2.2
Employee 1 Employee 1.3    Employee 1.3 ->Employee 1->Employee 1.3

The SQL to do that is

WITH sample_data
AS
(
    SELECT TO_CHAR(NULL) AS Manager, 'Employee 1' AS Employee FROM DUAL
    UNION ALL
    SELECT 'Employee 1' AS Manager, 'Employee 1.1' AS Employee FROM DUAL
    UNION ALL
    SELECT 'Employee 1' AS Manager, 'Employee 1.2' AS Employee FROM DUAL
    UNION ALL
    SELECT 'Employee 1' AS Manager, 'Employee 1.3' AS Employee FROM DUAL
    UNION ALL
    SELECT 'Employee 1.2' AS Manager, 'Employee 1.2.1' AS Employee FROM DUAL
    UNION ALL
    SELECT 'Employee 1.2' AS Manager, 'Employee 1.2.2' AS Employee FROM DUAL
    UNION ALL
    SELECT 'Employee 1.2.1' AS Manager, 'Employee 1.2.1.1' AS Employee FROM DUAL
    UNION ALL
    SELECT 'Employee 1.2.1' AS Manager, 'Employee 1.2.1.2' AS Employee FROM DUAL
    UNION ALL
    SELECT 'Employee 1.2.1' AS Manager, 'Employee 1.2.1.3' AS Employee FROM DUAL
)
SELECT
    manager,
    employee,
    --Depending on the CONNECT BY LEVEL, prefix with respective number of spaces
    case when level = 1 then employee
            else lpad (' ', 3*(level-1)) || employee
        end nicer_view,
    sys_connect_by_path(employee, '->') even_nicer_view
FROM sample_data
    START WITH Manager IS NULL
    CONNECT BY Manager= PRIOR Employee

Although this illustrates the classic “manager” and “employee” relationship, it also lends itself very well to any hierarchical relationship like bill of materials, nested menu structures used in a website, or nested role/user hierarchy in system tables etc. The applications are numerous.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s