Home » SQL & PL/SQL » SQL & PL/SQL » Question about printing the result of a recursive subquery factoring with line indents
icon5.gif  Question about printing the result of a recursive subquery factoring with line indents [message #665307] Tue, 29 August 2017 10:15 Go to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Hello

Environment:
OS: CentOS 7 3.10.0-514.26.2.el7.x86_64 
Oracle version: 12c Enterprise Edition Release 12.2.0.1.0 - 64bit

Test case: Oracle HR schema's employees table


Requirement:
Write a query printing the employees and their managers in an hierarchical way, with line indents based on the LEVEL, something like:
emp-01 -- is a manager
    emp-02
    emp-03
    emp-04 -- is a manager
        emp-06
        emp-07
    emp-05
       .
       .
       .


As a first solution, here what I wrote for the above mentioned problem:
column employee_id format a20
column department_id format 999



select
    lpad(' ', (4 * (level - 1)), ' ') || t1.employee_id as employee_id,
    t1.department_id
from
    hr.employees t1
start with 
    t1.manager_id is null
connect by
    prior t1.employee_id = t1.manager_id
order siblings by
    t1.employee_id asc;

Currently I'm reading the oracle online documentation in order to learn how to rewrite the query by using Recursive Subquery Factoring and so far it has given me hell every step of the way for both understanding the concept and writing the query.

What I understand (please correct me if I'm wrong) is that the so-called anchor part of the query corresponds to the START WITH and what comes after UNION ALL in the recursive part is like CONNECT BY. This is what I have written:
column employee_id format a20
column department_id format 999


with enterprise_hierarchy(employee_id, manager_id, department_id, lvl) as
(
    select
        t1.employee_id,
        t1.manager_id,
        t1.department_id,
        0 as lvl
    from
        hr.employees t1
    where
        t1.manager_id is null
    union all
    select
        t2.employee_id,
        t2.manager_id,
        t2.department_id,
        t3.lvl + 1
    from
        hr.employees t2
    inner join
        enterprise_hierarchy t3
    on
        t3.employee_id = t2.manager_id
)
select
    lpad(' ', (4 * (lvl - 1)), ' ') || t4.employee_id as employee_id,
    t4.department_id
from
    enterprise_hierarchy t4
order by
    t4.lvl asc,
    t4.employee_id asc;
Apparently the result in terms of rows and content is the same, except that I don't know what to put exactly in ORDER BY clause in order to obtain the same indented form presented above in the first query (the one with CONNECT BY).

Any idea?


Thanks in advance,
Re: Question about printing the result of a recursive subquery factoring with line indents [message #665317 is a reply to message #665307] Tue, 29 August 2017 17:30 Go to previous message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Well, it seems that the answer was in the same page of the documentation and I didn't pay attention.
https://docs.oracle.com/database/122/SQLRF/SELECT.htm#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__BCEJGIBG
Quote:
search_clause

Use the SEARCH clause to specify an ordering for the rows.

* Specify BREADTH FIRST BY if you want sibling rows returned before any child rows are returned.

* Specify DEPTH FIRST BY if you want child rows returned before any siblings rows are returned.

* Sibling rows are ordered by the columns listed after the BY keyword.

* The c_alias list following the SEARCH keyword must contain column names from the column alias list for query_name.

* The ordering_column is automatically added to the column list for the query name. The query that selects from query_name can include an ORDER BY on ordering_column to return the rows in the order that was specified by the SEARCH clause.


So I changed the query in the following way (for those who may have encountered the same problem)
with enterprise_hierarchy
(
    employee_id, 
    manager_id, 
    department_id,
    lvl
) 
as
(
    select
        t1.employee_id,
        t1.manager_id,
        t1.department_id,
        0 as lvl
    from
        hr.employees t1
    where
        t1.manager_id is null
    union all
    select
        t2.employee_id,
        t2.manager_id,
        t2.department_id,
        t3.lvl + 1
    from
        hr.employees t2
    inner join
        enterprise_hierarchy t3
    on
        t3.employee_id = t2.manager_id
)
search depth first by employee_id set order1
select
    lpad(' ', (4 * lvl), ' ') || to_char(t4.employee_id) as employee_id
from
    enterprise_hierarchy t4
order by
    order1 asc;
Which provided the desired result.
Previous Topic: Invalid Datatype error for record type
Next Topic: REGEXP_INSTR
Goto Forum:
  


Current Time: Fri Mar 29 06:14:51 CDT 2024