SQL> select * from R_Rightsassociation; Right.5 Right.3 Right.3 Right.4 Right.4 Right.2The result set I want is:
Right.5 Right.3 Right.5 Right.4 Right.5 Right.2 Right.3 Right.4 Right.3 Right.2 Right.4 Right.2(I'm doing this because the desired result set will be used for super fast, simple select queries through a simple where clause).
What oracle SQL query would you suggest to get the desired result set?
SYS_CONNECT_BY_PATH. Here's what he meant:
SELECT SUBSTR ( path , 1 , INSTR ( path , ' / ' -- This separator must -- not occur in names ) - 1 ) AS ancestor , child FROM ( -- Begin in-line view to derive path SELECT parent , child , SUBSTR ( SYS_CONNECT_BY_PATH ( parent , ' / ' ) , 1 + LENGTH (' / ') ) -- Don't want the separator -- at the beginning || ' / ' AS path -- Separator at end ensures that INSTR -- (above )never returns 0 FROM r_rightsassociation CONNECT BY parent = PRIOR child ) -- End in-line view to derive path ORDER BY ancestor , child ;I assume that the table r_rightsassociation contains two columns,
child, and that the ideal result set shows all ancestor/descendant relationships. Oracle has always supported hierachical queries, based on this kind of tree structure, but until version 9 the ancestor/descendant relationship was only expressed by the
LEVELpseudo-column and the relative position of rows in the result set. In version 9, Oracle introduced the
SYS_CONNECT_BY_PATHfunction, which makes it much easier to show ancestor/descendant relationships on each row, which is exactly what you want.
SYS_CONNECT_BY_PATH returns a string, consisting of a given column from each level of the hierarchy, starting with the root (where
LEVEL = 1) and ending with the current row. Each of these column values is preceded by a separator string. Each row's ultimate ancestor is represented by the first item in this string. The solution above assumes that the string
' / ' never occurs as part of a
parent; if it does, pick some other string. It doesn't really matter how long or ugly, since you never display it.
This query has no
START WITH clause, so every row in the table will be treated as a root.
In your sample data, each node has (at most) one parent and (at most) one child. The solution works even if a parent has several children (e.g., if you add a row
Right.3 Right.1) or if a child has several parents (
Right.6 Right.3). If both of these conditions are true (
Right.5 Right.4) the solution still works, but the result set includes n identical rows when there are n separate paths by which an ancestor is related to a descendant. If that's a problem, simply change the main query to begin with
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.