I have a table containing data which may be represented in this pseudo directed graph: I say it is a pseudo-graph because I have some «edges» which are only connected on 1 node.
Each «edges» are labelled, and will be refereed to as an event.
Each nodes contain only one ID.
The Oracle (12c) table look like this: http://sqlfiddle.com/#!4/79cdb5/4/0
on the Table I ran this query and one of the row I expected to have a 1 in its pseudo column CONNECT_BY_ISLEAF has instead a 0.
This is the guilty row: http://sqlfiddle.com/#!4/79cdb5/3/2
I am completely unable to understand why oracle does not consider this row to be a leaf when it obviously is one.
In the table I am using each row represent an event (or graph edge) plus the node(s) to which it is connected.
the VUID column is the “previous” node, the AUID is «step», the EVENT is the event label, the NEW_VUID is the “next” node.
Exception is for event D and U which only have 1 node connected to it and this node will always be in the VUID column (even if for D event the node is the “next” node).
Here I will just give some context about the request I am doing
My final aim is to recreate this graph based on the data there is in the Table. For that I proceed in steps:
During step 1. I should end up with forward trees looking like that:
To create the trees with oracle I think the easiest would be to make the right hierarchical query and then use the pseudo column SYS_CONNECT_BY_PATH and filter on CONNECT_BY_ISLEAF = 1, this is because using only leaf plus the path each leaf had it is easy to re-create the tree.
However I am stuck because for some reason I do not understand Oracle is not considering all leaf the same way I do. The leaf containing the node 88888 is not considering
I didn't take the time to fully understand your data model and would suggest that you might have difficulty accomplishing your goal in an understandable manner without some sort of primary key in your table. CONNECT BY
is one of the more advanced query forms in Oracle and having a traditional PRIOR t.id = t.parent_id
relationship makes it easier.
Anyway, the reason for the results that you find confusing is because you have this row in your data:
into TEST_HISTORY values (88888, 3, 'U', null)
It is a "child" of the row you think is a leaf, making that row actually not a leaf at all.
Run your query without the WHERE
clause and you should see it. The CONNECT BY
happens before the WHERE
clause. Filtering out leaves in the WHERE
clause does not make their now-childless parents into leaves.