EMP table data.

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

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.

Leave a Reply