I am using Oracle 12c.
I have an Oracle table that is hierarchical where I would like to use the short name of the parent node (i.e. start with parent_node_is is null)
for all children nodes that belong to that parent.
For instance: Table Name: nodes_tab
NODE_ID SHORT_NAME PARENT_NODE_ID
---------- -------------- --------------
1 Parent Node-1 NULL
2 Child Node-2 1
3 Child Node-3 1
4 Child Node-4 2
5 Child Node-5 2
6 Child Node-6 4
7 Child Node-7 6
What I would like to achieve is to query the above nodes_tab for all node_ids but assign the short_name belonging to the parent node.
Ideally I just want to repeat the same name of Parent Node-1 for the remaining node_ids from 2 down to 7 but unsure what the SQL query should be. I looked at LAG but it doesn't seem to do the trick.
Result I am after is:
NODE_ID SHORT_NAME
---------- -------------
1 Parent Node-1
2 Parent Node-1
3 Parent Node-1
4 Parent Node-1
5 Parent Node-1
6 Parent Node-1
7 Parent Node-1
Hierarchically, your data looks like this:
SQL> with nodes_tab (node_id, short_name, parent_node_id) as
2 (select 1, 'Parent Node-1', null from dual union all
3 select 2, 'Child Node-2' , 1 from dual union all
4 select 3, 'Child Node-3' , 1 from dual union all
5 select 4, 'Child Node-4' , 2 from dual union all
6 select 5, 'Child Node-5' , 2 from dual union all
7 select 6, 'Child Node-6' , 4 from dual union all
8 select 7, 'Child Node-7' , 6 from dual
9 )
10 select node_id,
11 lpad(' ', 2 * level) || short_name as short_name,
12 parent_node_id,
13 connect_by_root short_name as root_node
14 from nodes_tab
15 start with parent_node_id is null
16 connect by prior node_id = parent_node_id;
NODE_ID SHORT_NAME PARENT_NODE_ID ROOT_NODE
---------- ------------------------- -------------- -------------
1 Parent Node-1 Parent Node-1
2 Child Node-2 1 Parent Node-1
4 Child Node-4 2 Parent Node-1
6 Child Node-6 4 Parent Node-1
7 Child Node-7 6 Parent Node-1
5 Child Node-5 2 Parent Node-1
3 Child Node-3 1 Parent Node-1
7 rows selected.
SQL>
Note the ROOT_NODE, which is fetched by using CONNECT_BY_ROOT - it seems that you want that value for all SHORT_NAMEs.
So: if we remove indentation and apply what we've seen above, along with appropriate ORDER BY clause, the final result is
SQL> with nodes_tab (node_id, short_name, parent_node_id) as
2 (select 1, 'Parent Node-1', null from dual union all
3 select 2, 'Child Node-2' , 1 from dual union all
4 select 3, 'Child Node-3' , 1 from dual union all
5 select 4, 'Child Node-4' , 2 from dual union all
6 select 5, 'Child Node-5' , 2 from dual union all
7 select 6, 'Child Node-6' , 4 from dual union all
8 select 7, 'Child Node-7' , 6 from dual
9 )
10 select node_id,
11 connect_by_root short_name as short_name
12 from nodes_tab
13 start with parent_node_id is null
14 connect by prior node_id = parent_node_id
15 order by node_id;
NODE_ID SHORT_NAME
---------- -------------------------
1 Parent Node-1
2 Parent Node-1
3 Parent Node-1
4 Parent Node-1
5 Parent Node-1
6 Parent Node-1
7 Parent Node-1
7 rows selected.
SQL>