Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Recursive Subquerying with sorting.

JGFMKApr 20 2018 — edited Apr 23 2018

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)

This post has been answered by mathguy on Apr 20 2018
Jump to Answer

Comments

fac586

Apex-User wrote:

A working example is here.  If you open department one, then select all the teams, then deselect/select them a couple of times you'll see the state of the parent node gets confused.

Apex is version 4.2.3

Can't reproduce the problem as described in Chrome 44.0.2403.157 or IE 9.0.8112.16421. What browser(s)/version(s) are you using?

There are a number of errors logged in the console on page load that may be relevant:

https://apex.oracle.com/pls/apex/public/r/files/static/v1Y/cidb-styles.css Failed to load resource: the server responded with a status of 404 (Not Found)

https://apex.oracle.com/pls/apex/public/r/files/static/v1Y/toggles-full.css Failed to load resource: the server responded with a status of 404 (Not Found)

https://apex.oracle.com/pls/apex/public/r/files/static/v1Y/toggles.js Failed to load resource: the server responded with a status of 404 (Not Found)

https://apex.oracle.com/pls/apex/public/r/65129/files/static/v1Y/ci-logo3.png Failed to load resource: the server responded with a status of 404 (Not Found)

f:57 The value "device-width;" for key "width" is invalid, and has been ignored.

f:57 The value "1.0;" for key "initial-scale" was truncated to its numeric prefix.

f:57 The value "1.0;" for key "maximum-scale" was truncated to its numeric prefix.

f:57 Error parsing a meta element's content: ';' is not a valid key-value pair separator. Please use ',' instead.

f:199 Uncaught TypeError: $(...).toggles is not a function

https://apex.oracle.com/pls/apex/public/r/65129/files/static/v1Y/ci-logo3.png Failed to load resource: the server responded with a status of 404 (Not Found)

https://apex.oracle.com/pls/apex/public/r/files/static/v1Y/toggles-full.css Failed to load resource: the server responded with a status of 404 (Not Found)

https://apex.oracle.com/pls/apex/public/r/files/static/v1Y/cidb-styles.css Failed to load resource: the server responded with a status of 404 (Not Found)

And some further errors following interaction with the tree:

apex.jquery.tree.min.js:14 Uncaught TypeError: a.curCSS is not a function

https://apex.oracle.com/pls/apex/public/r/files/static/v1Y/cidb-styles.css Failed to load resource: the server responded with a status of 404 (Not Found)

https://apex.oracle.com/pls/apex/public/r/files/static/v1Y/toggles-full.css Failed to load resource: the server responded with a status of 404 (Not Found)

Are these (or other errors) occurring on your local installation?

Apex-User

Sorry about the errors, they are caused by missing resources where I've had to export/import a demo app into Apex 5 to retain the old jstree. I've removed them all now other than a few warnings about a bad meta statement. Issue is still there for me.

I'm developing in IE8 (yuck I know) but currently testing in Chrome Version 44.0.2403.157.

The error is certainly still there:

  • Expand Company One
  • Expand Department One
  • Tick Team D1 One, Team D1 Two and Team D1 Three (parent is now checked)
  • Untick Team D1 One (parent is now undetermined)
  • Tick Team D1 One (parent stays undetermined though all children are selected)

Thanks!

Apex-User

No other clever jQuery experts able to offer any clues?

Thanks.

Tom Petrus

The line you identified as probable error is indeed the error. It's weird. It means that if a node is unchecked and it has no child nodes with the unchecked class (read: the li's child anchor node which has the classes assigned) then all nodes in the tree are being flagged as unchecked... That's wrong indeed.

Try this:

open up company one, open department one. Check team D1 one, two and three. Uncheck three. Check three again and the parent remains undetermined. Now uncheck and check two and one. The parent goes checked.

This is because as as soon as you have unchecked three after checking all three nodes, all nodes get the unchecked class. Including one and two, even though they have checked. Obviously that means that the state of the parent checkbox can't be determined by checking the classes. Unchecking and checking the other two nodes will set their classes correctly again (rather, the anchor tags). Though if you were to uncheck right away again it'll be broken all over once more.

I'm not entirely sure what the point of that line of code was.

I'm not sure of the fix either. I really don't want to go and copy code and set up a tree etc etc. I'm not sure if this if ($this.children("a.unchecked").size() == 0) is necessary.

Try replacing the code in the if with this:

$this.find("a").addClass("unchecked");

That should at least confine it to the current node.

Some extra notes:

- the js errors on the page are really annoying. A simpler example with just this checkbox implementation would help better

- your tree LI nodes are being generated with an ID which contains spaces. That's a nightmare to be debugging with.

Apex-User

Thanks Tom.  The code change didn't quite fix it.. checking the first leaf node also checked the parents instead of making them undetermined.

In the end, I removed the 'if' statement all together and added this new bind to mark everything as unchecked on tree load:

onload: function(TREE_OBJ) {

    TREE_OBJ.container.find("a").addClass("unchecked");

  }

So far this seems to be working.

Tom Petrus

I supposed it was to handle an initial state, but couldn't really make sure. It's why I wasn't sure what it was really doing there, it seemed like a wrong place. Good call on moving it to the onload.

1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 21 2018
Added on Apr 20 2018
7 comments
607 views