Oracle SQL Query to Get Hierarchical Tree

Oracle SQL Query to Get Hierarchical Tree

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 1 min read

In this Oracle SQL query example, you will learn how to get the hierarchical tree from the table data.

The below example is given on the EMP table of the SCOTT schema. Below is the screenshot of the data:

EMP table data.

Oracle SQL Query to Get Hierarchical Tree Data of EMP Table

I assume you requiring a hierarchical tree query to use in the Oracle Forms or in the Oracle Apex application. If yes then the below SQL query example is for you:

SELECT
    CASE
    WHEN CONNECT_BY_ISLEAF = 1  THEN
    0
    WHEN LEVEL = 1              THEN
    1
    ELSE
    - 1
    END                 AS STATUS,
    LEVEL,
    ENAME               AS TITLE,
    'icon-tree-folder'  AS ICON,
    EMPNO               AS VALUE,
    ENAME               AS TOOLTIP,
    '#'                 AS LINK
FROM
    EMP
START WITH
    MGR IS NULL
CONNECT BY
    PRIOR EMPNO = MGR
ORDER SIBLINGS BY
    ENAME