sql - Recursive Subquerying with sorting

Question

I looked at Tim Hall's excellent article here, that allows you to work with self-referenced entities and show hierarchical data (starting with top level nodes and joining back recursively), using CTE like syntax in Oracle.

So I have code that looks like this:

WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS  (   
  SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1   
  FROM TIDAL.JOBMST   
  WHERE JOBMST_PRNTID IS NULL   
  UNION ALL   
  SELECT J2.JOBMST_ID,J2.JOBMST_NAME,J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL+1    
  FROM TIDAL.JOBMST J2    
  INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID    
  WHERE J2.JOBMST_PRNTID IS NOT NULL)    
SEARCH DEPTH FIRST BY JOBMST_ID SET DISP_SEQ
SELECT *
FROM J1
ORDER BY DISP_SEQ

For the the anchor rows (the top level hierarchy J1 entries in my SQL, with NULL parents), I would like to:

 ORDER BY  J1.JOBMST_NAME 

For the recursive joins:

ORDER BY J2.JOBMST_PRNTID, J2.JOBMST_NAME

• If I try and add an ORDER BY statement above the UNION ALL statement I get some sort of invalid SQL syntax.

• How do you work around that, so the data in the end is sorted alphabetically by name, at each level of depth in the hierarchy?

• (If the data is sorted correctly at the point of the joins, the DISP_SEQ created by SEARCH DEPTH FIRST should collate the data correctly).

You end up with something like this (names omitted):

JOBMST_ID JOBMST_NAME JOBMST_PRNTID JOBMST_TYPE LVL DISP_SEQ
 746                                1           1   1
1433                                1           1   2
1328                   1433         1           2   3
1329                   1328         1           3   4
1330                   1329         1           4   5
1331                   1329         1           4   6
1332                   1329         1           4   7

My goal:

• All level 1's are sorted alphabetically by JOBMST_NAME
• All level 2's within a level 1 are sorted alphabetically by JOBMST_NAME per parent
• All level 3's within a level 2 are sorted alphabetically by JOBMST_NAME, per parent,
• etc, etc..

---

Update: I have managed to tweak the code somewhat, so the anchor select is sorted:

But I can't seem to apply same syntactic sugar to the recursive join.

WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS  (
  SELECT * FROM (
    SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1
    FROM TIDAL.JOBMST
    WHERE JOBMST_PRNTID IS NULL
    ORDER BY JOBMST_NAME
  )
UNION ALL
SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL + 1
FROM TIDAL.JOBMST J2
INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID
WHERE J2.JOBMST_PRNTID IS NOT NULL
)
SEARCH DEPTH FIRST BY JOBMST_ID SET DISP_SEQ
SELECT *
FROM J1
ORDER BY DISP_SEQ

Answer 1

Initially, I could not see a more elegant solution than to create a temp table.

I was thinking, what a awkward dialect of SQL Oracle is:

1. Why no IF TABLE EXISTS DELETE TABLE?
2. Why do I have to do EXECUTE IMMEDIATE with a string? Why can't I just do DROP TABLE TEMP on its own?
3. Why can't I have ORDER BY without nesting in parentheses on ANCHOR?
4. Why can't I have ORDER BY on recursive SELECT after UNION ALL?
5. SQL WITH needs standardising. Other database dialects don't necessitate column names being parenthesised on WITH statement. If you don't do that you get some meaningless ALIAS error, at the point of the recursive join after UNION ALL.
6. Pagination: See here No LIMIT / OFFSET

---

DECLARE
 v_c NUMBER;
BEGIN
SELECT COUNT(*) INTO v_c FROM user_tables WHERE TABLE_NAME = 'TEMP';
IF v_c = 1 THEN
  EXECUTE IMMEDIATE 'DROP TABLE TEMP';
END IF;
END;
CREATE TABLE TEMP AS  (
    SELECT * FROM (
      SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE
      FROM TIDAL.JOBMST
      WHERE JOBMST_PRNTID IS NOT NULL
      ORDER BY JOBMST_PRNTID, JOBMST_NAME
    )
);
WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS  (
  SELECT * FROM (
    SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1
    FROM TIDAL.JOBMST
    WHERE JOBMST_PRNTID IS NULL
    ORDER BY JOBMST_NAME
  )
UNION ALL
SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL + 1
FROM TEMP J2
INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID
WHERE J2.JOBMST_PRNTID IS NOT NULL
)
SEARCH DEPTH FIRST BY JOBMST_ID SET DISP_SEQ
SELECT *
FROM J1
ORDER BY DISP_SEQ;

---

Then (mathguy on the Oracle Community Forum) pointed out to me my SEARCH DEPTH FIRST should have just been by JOBMST_NAME.

Then it all falls into place:

WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS  (
    SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1
    FROM TIDAL.JOBMST
    WHERE JOBMST_PRNTID IS NULL
UNION ALL
SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL + 1
FROM TIDAL.JOBMST J2
INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID
WHERE J2.JOBMST_PRNTID IS NOT NULL
)
SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
SELECT *
FROM J1
ORDER BY DISP_SEQ