[SQL] In a hierarchical SQL query, how to remove the children of a row that does not satisfy a...

SELECT rep.*,
rank() OVER (PARTITION BY rep.ASSEMBLY_ID
ORDER BY CASE WHEN rep.state_code = 'PUBLISH' THEN 1 ELSE 2 END) rnk
FROM
(SELECT *
FROM TABLE T
START WITH T.SCENARIO_ID = <SCENARIO_ID>
CONNECT BY PRIOR T.COMPONENT_ID = T.ASSEMBLY_ID
)rep WHERE rep.rnk = 1


Above query builds the hierarchy of Item Structure where the components of Assembly A are parents of other assemblies. In that hierarchy I want to select only those assemblies which are ranked as 1 as per the "STATE_CODE" which can be "PUBLISH" or "ROLLUP" for the parent nodes in the hierarchy. I am able to acheive it with RANK function but I am not able to restrict the child rows for the assemblies with RANK other than 1. Any input on how we can restrict the child rows by bringing the rank function within the CONNECT BY OPERATOR so query executes CONNECT BY operation only when the assembly is ranked 1

Continue reading...