CODEWITHSUNDEEP
sqloracle-databasesortingoracle11ghierarchical-data
AngryPanda
AngryPanda

Reputation: 1281

Sorting with Level in Oracle11g

I am trying to create an organizational structure using LEVEL functionality in Oracle11g.

I have the following query:

SELECT
  level,
  lpad(' ', 3 * (level - 1)) || em.empno || ' : ' || em.fname || ' ' || em.lname "Employee",
  em.position                                                                    "Position",
  outno || ': ' || ou.street || ' ' || ou.city || ' ' || ou.zipcode              "Outlet",
  count(DISTINCT fa.reportnum)                                                   "# of Fault Reports"
FROM employee em
  INNER JOIN outlet ou USING (outno)
  LEFT JOIN faultreport fa ON fa.empno = em.empno AND fa.datechecked > (SYSDATE - 91)
START WITH em.empno = 30012
CONNECT BY PRIOR em.empno = em.supervisorno
GROUP BY level,
  em.empno, em.fname, em.lname, em.position,
  outno, ou.street, ou.city, ou.zipcode;

Which gives me the following output:

LEVEL Employee                          Position             Outlet                         # of Fault Reports
----- --------------------------------- -------------------- ------------------------------ ------------------
    1 30012 : Moreno Bale               Owner                119: Forrest Adelaide 5005                      0
    2    45611 : Annah Marlek           Area_Manager         112: Beauford Port Cambia 5001                  0
    2    48900 : Geoff Hanna            Area_Manager         118: Icecream Iyanee 5008                       0
    3       23490 : Abel Cole           Admin_Assistant      116: Huntington Banshee 5007                    0
    3       31459 : Chris Boss          Admin_Assistant      119: Forrest Adelaide 5005                      0
    3       60021 : Beau Rueford        Admin_Assistant      111: Junlee Caprice 5009                        0
    3       67823 : Jess Fred           Head_Mechanic        114: Elephant Ocupus 5004                       0
    4          55601 : Kabil Malla      Mechanic             115: Dundee Eeyrie 5003                         1
    4          55602 : Harry Potter     Mechanic             111: Junlee Caprice 5009                        5
    4          60020 : Maria Marbosa    Sales_Rep            113: Cathany Zeus 5002                          0
    4          77689 : Javier Martin    Sales_Rep            112: Beauford Port Cambia 5001                  0

11 rows selected.

However, when I see the table, this is the exact relationship:

Employee    Manager
    30012   -
    48900   30012
    45611   30012
    23490   45611
    31459   48900
    67823   48900
    55602   67823
    55601   67823
    60021   48900
    77689   60021
    60020   60021

How do I implement this in Oracle so that I have the following output:

LEVEL Employee                          Position             Outlet                         # of Fault Reports
----- --------------------------------- -------------------- ------------------------------ ------------------
    1 30012 : Moreno Bale               Owner                119: Forrest Adelaide 5005                      0
    2    45611 : Annah Marlek           Area_Manager         112: Beauford Port Cambia 5001                  0
    3       23490 : Abel Cole           Admin_Assistant      116: Huntington Banshee 5007                    0
    2    48900 : Geoff Hanna            Area_Manager         118: Icecream Iyanee 5008                       0
    3       31459 : Chris Boss          Admin_Assistant      119: Forrest Adelaide 5005                      0
    3       60021 : Beau Rueford        Admin_Assistant      111: Junlee Caprice 5009                        0
    4          60020 : Maria Marbosa    Sales_Rep            113: Cathany Zeus 5002                          0
    4          77689 : Javier Martin    Sales_Rep            112: Beauford Port Cambia 5001                  0
    3       67823 : Jess Fred           Head_Mechanic        114: Elephant Ocupus 5004                       0
    4          55601 : Kabil Malla      Mechanic             115: Dundee Eeyrie 5003                         1
    4          55602 : Harry Potter     Mechanic             111: Junlee Caprice 5009                        5

11 rows selected.

Note that the if a manager manages more than 1 employee, then the tree is expanded based on employee number.

Thanks!

Upvotes: 3

Views: 73

Answers (1)

Alessandro Rossi
Alessandro Rossi

Reputation: 2450

The explanation of your data lacks of many details. Anyway by the query you tried to execute and your excepted output, I suppose that the mistake was that you tried to do to many things at once: aggregation and hierarchical query together.

To get your desired output the connect by should be the last step of the query evaluation, but the aggregation can't be evaluated before it in a query.

The connect by evaluation returns rows in your expected order, but the grouping clause, witch defines an implicit sort in its evaluation, overrides it giving the rows ordered by the grouping keys.

Generally SQL statements processing evaluates query clauses in the following order:

  1. FROM + WHERE + CONNECT BY
  2. GROUP BY
  3. HAVING
  4. SELECT
  5. ORDER BY

So you should separate the two things through a subquery that evaluates the count aggregation before executing the hierarchical query phase without sorting the output.

You may try this query:

SELECT LEVEL, LPAD(' ', 3*(LEVEL - 1)) || empno || ' : ' || EM.fname || ' ' || EM.lname "Employee", 
    EM.position "Position", outno || ': ' || OU.street || ' ' || OU.city || ' ' || OU.zipcode "Outlet", 
    dist_reportnums "# of Fault Reports" 
FROM employee EM
    JOIN outlet OU using (outno) 
    LEFT JOIN (
            selec empno, COUNT(DISTINCT FA.reportnum) as dist_reportnums
            from faultreport FA
            where FA.datechecked > (SYSDATE - 91)
            GROUP BY empno
        ) using (empno)
START WITH empno = 30012
CONNECT BY PRIOR empno = EM.supervisorno

Upvotes: 3

Related Questions