Skip to content
Advertisement

Recursive Subquerying with sorting

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:

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

For the recursive joins:

  • 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):

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.

Advertisement

Answer

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


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:

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement