Recursive CTE on every row in a stored procedure with parameter

Question

I have a table called AccountNode. Where the the column nodeId has a parent in the ParentNodeId col.

AccountNode
nodeId   | ParentNodeId  |  Flag | SetId | 

1           2              N     1 
2           115            N     1 
115         4              N     1
4           5              Y     1
12          13             N     1 
13          14             N     1 
14          15             Y     1 
23          24             N     1 
25          30             Y     1

What i need : i need to get the parent node of each node id where the flag is Y (this is where we need to stop our recursive cte), for the setId passed as parameter to the procedure.

eg : 
input: 
for set_id : 1 
output: 

nodeId    parentNode  flag    set_id
1           5           Y        1 
12          15          Y        1 
25          30          Y        1

i have written a recursive cte to get the parent node for a node id , but i am having trouble wrting it for a setid where , i need to loop through all the nodeIds in a setid , to get the parentNode. Here is my sql :

with accountNode_cte  (nodeId, parentNode, flag, set_id) as 
(select nodeId , parentNode, flag, set_id) from accountNode where nodeId = '1' 
union all
select accountNode.nodeId, accountNode.parentNode, accountNode.flag, accountNode.set_id from 
accountNode 
join accountNode_cte on accountNode.nodeId = accountNode_cte.parentNode
and accountNode_cte.flag !='Y')
select * from accountNode_cte where flag='Y'

i am pretty new to writing sql procedures , not sure how to go about this

Once you have the query working, where does the PL/SQL procedure come in? — William Robertson, Jul 18 '21 at 08:31
This query gives correct result for a nodeId. Was stuck when when a setId comes into picture which has multiple nodeIds under it. — Lavina Khushlani, Jul 20 '21 at 17:44

score 1 · Accepted Answer · answered Jul 18 '21 at 08:23

Traverse the node hierarchy from the starting nodes, remember starting point, track level, take max within starting point level rows.

with accountNode_cte(p, l, nodeId, parentNode, flag, set_id) as (
  select parentNodeId p, 1 l, nodeId , parentNodeId, flag, set_id 
  from accountNode 
  where flag = 'Y' and set_id = 1
  
  union all
  
  select p, l+1, accountNode.nodeId, accountNode.parentNodeId, accountNode.flag, accountNode.set_id 
  from accountNode 
  join accountNode_cte on accountNode.parentnodeId = accountNode_cte.NodeId
)
select distinct first_value(nodeId) over(partition by p order by l desc) nodeId, p parentnId
from accountNode_cte;

db<>fiddle

Recursive CTE on every row in a stored procedure with parameter

1 Answers1