Skip to content

Instantly share code, notes, and snippets.

@jsmpros

jsmpros/PL_CONTAINING_REC.sql

Created July 20, 2022 20:18
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jsmpros/f2ee8c2873316509f9a4756e3b48ec8e to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jsmpros/f2ee8c2873316509f9a4756e3b48ec8e to your computer and use it in GitHub Desktop.
Download ZIP
Raw
PL_CONTAINING_REC.sql
SELECT DISTINCT PL.CLASSID
, PL.TREE_NAME
, PL.ACCESS_GROUP
FROM PS_SCRTY_ACC_GRP PL
INNER JOIN (
SELECT TREE_NAME
, TREE_NODE /* The node currently at the top of the path */
, RTRIM(REVERSE(SYS_CONNECT_BY_PATH(REVERSE(TREE_NODE), ' >> ')), ' >> ') /* Not required, but nice for troubleshooting */ PATH
FROM (
SELECT TREE_NAME
, TREE_NODE
, TREE_NODE_TYPE
, PARENT_NODE_NAME
FROM PSTREENODE TN
WHERE TN.EFFDT = (
SELECT MAX(TN_ED.EFFDT)
FROM PSTREENODE TN_ED
WHERE TN_ED.SETID = TN.SETID
AND TN_ED.SETCNTRLVALUE = TN.SETCNTRLVALUE
AND TN_ED.TREE_NAME = TN.TREE_NAME
AND TN_ED.EFFDT <= SYSDATE )
)
START WITH TREE_NODE = 'PERSONAL_DATA' /* The bottom of the path. Replace with the target record */
AND TREE_NODE_TYPE ='R'
CONNECT BY PRIOR PARENT_NODE_NAME = TREE_NODE ) TREE
ON TREE.TREE_NAME = PL.TREE_NAME
AND TREE.TREE_NODE = PL.ACCESS_GROUP
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment