|
SELECT N.SETID ,N.TREE_NAME ,N.EFFDT ,V.TREE_LEVEL_NUM ,V.TREE_LEVEL ,N.TREE_BRANCH ,N.TREE_NODE ,A.DESCR ,L.RANGE_FROM ,L.RANGE_TO FROM PSTREELEAF L , PSTREENODE N , PSTREEDEFN T , PSTREELEVEL V ,PS_TREE_NODE_TBL A WHERE T.EFF_STATUS = 'A' AND N.SETID = T.SETID AND N.TREE_NAME = T.TREE_NAME AND N.EFFDT = T.EFFDT AND V.SETID = N.SETID AND V.TREE_NAME = N.TREE_NAME AND V.EFFDT = N.EFFDT AND V.TREE_LEVEL_NUM = N.TREE_LEVEL_NUM AND L.SETID = N.SETID AND L.TREE_NAME = N.TREE_NAME AND L.EFFDT = N.EFFDT AND L.TREE_BRANCH = N.TREE_BRANCH AND L.TREE_NODE_NUM BETWEEN N.TREE_NODE_NUM AND N.TREE_NODE_NUM_END AND A.SETID = N.SETID AND A.TREE_NODE = N.TREE_NODE AND N.EFFDT = ( SELECT MAX(W.EFFDT) FROM PSTREENODE W WHERE W.SETID = N.SETID AND W.SETCNTRLVALUE = N.SETCNTRLVALUE AND W.TREE_NAME = N.TREE_NAME AND W.TREE_NODE_NUM = N.TREE_NODE_NUM AND W.TREE_NODE = N.TREE_NODE AND W.EFFDT <= %CurrentDateIn) AND A.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_TREE_NODE_TBL X WHERE X.SETID = A.SETID AND X.TREE_NODE = A.TREE_NODE AND X.EFFDT <= N.EFFDT) AND A.EFF_STATUS = 'A'
|