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
Now I would like to effectively ORDER BY J1.JOBMST_NAME for the the anchor rows (the top level hierarchy J1 entries in my SQL, with NULL parents)
and in the point where it joins back recursively ORDER BY J2.JOBMST_PRNTID, J2.JOBMST_NAME.
But, 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 should collate the data correctly)