I have a table that looks like (Oracle 11.2.0.2.0):
ParentID ID AllowsEntry
NULL A188 N
A188 1881 Y
NULL A189 N
A189 1891 Y
1891 189A Y
Business rules allow for non-leaf level elements to have data entry, but I need to report as if they didn't I need to be able query the database to produce output like:
ParentID ID
NULL A188
A188 1881
NULL A189
A189 1891_
1891_ 189A
So basically I need to push down intermediate branches that allow data entry to the leaf level. The new leaves need to roll up to a renamed branch:
Old Tree New Tree
A188 A188 -- remains the same, no data entry at this level
1881 1881 -- remains the same, data entry allowed at leaf
A189 A189 -- remains the same, no data entry at this level
1891 1891_ -- this is the level that is wrong
189A 1891 -- 1891 is push down into a new 1891_ level
189A -- and rolls up into the new level.
189B -- etc.
Thanks for the help
I think you want to add a leaf element to nodes that have AllowsEnrtry = 'Y'. The node element should also be renamed with a trailing underscore.
You can simulate the modified data with a UNION ALL and run the recursive query on this view:
SQL> WITH DATA AS (
2 SELECT NULL parentid, 'A188' ID, 'N' allowsEntry FROM dual
3 UNION ALL SELECT 'A188', '1881', 'Y' FROM dual
4 UNION ALL SELECT NULL , 'A189', 'N' FROM dual
5 UNION ALL SELECT 'A189', '1891', 'Y' FROM dual
6 UNION ALL SELECT '1891', '189A', 'Y' FROM dual
7 UNION ALL SELECT '1891', '189B', 'Y' FROM dual
8 ), leaf_also_nodes AS (
9 SELECT *
10 FROM DATA
11 WHERE allowsEntry = 'Y'
12 AND ID IN (SELECT parentid FROM DATA)
13 ), data_plus AS (
14 SELECT d.parentid,
15 d.id,
16 CASE WHEN l.id IS NOT NULL THEN l.id || '_' ELSE d.id
17 END display_name
18 FROM DATA d
19 LEFT JOIN leaf_also_nodes l ON d.id = l.id
20 UNION ALL
21 SELECT ID, NULL, ID FROM leaf_also_nodes
22 )
23 SELECT rpad(' ', (LEVEL - 1) * 2, ' ') || display_name tree
24 FROM data_plus
25 START WITH parentid IS NULL
26 CONNECT BY (PRIOR ID = parentid);
TREE
-----------------------------------------
A188
1881
A189
1891_
189A
189B
1891