这里用到了Oracle的一个树形结构查询函数select *  from record START WITH A.TREE_NODE IN ('COST_CTR_10053')
CONNECT BY PRIOR A.TREE_NODE_NUM = A.PARENT_NODE_NUM.查出指定节点下的所以子节点然后连接leaf表，选出所有的叶子。

/* Formatted on 11/7/2015 11:05:57 PM (QP5 v5.267.14150.38573) */
SELECT B.RANGE_FROM
FROM PSTREELEAF B,
( SELECT DISTINCT A.SETID,
A.SETCNTRLVALUE,
A.TREE_NAME,
A.EFFDT,
A.TREE_NODE_NUM
FROM PSTREENODE A,
(SELECT A.SETID,
A.SETCNTRLVALUE,
A.TREE_NAME,
A.EFFDT
FROM PSTREEDEFN A
WHERE A.SETID = 'SHARE'
AND A.SETCNTRLVALUE = ' '
AND A.TREE_NAME = 'PLD_LOC_COMBO'
AND A.EFFDT =
(SELECT MAX (B.EFFDT)
FROM PSTREEDEFN B
WHERE A.SETID = B.SETID
AND A.SETCNTRLVALUE = B.SETCNTRLVALUE
AND A.TREE_NAME = B.TREE_NAME
AND B.EFF_STATUS = 'A'
AND B.EFFDT <= SYSDATE)) DEFN
WHERE A.SETID = DEFN.SETID
AND A.SETCNTRLVALUE = DEFN.SETCNTRLVALUE
AND A.TREE_NAME = DEFN.TREE_NAME
AND A.EFFDT = DEFN.EFFDT
START WITH A.TREE_NODE IN ('COST_CTR_10053')
CONNECT BY PRIOR A.TREE_NODE_NUM = A.PARENT_NODE_NUM) SA
WHERE SA.SETID = B.SETID
AND SA.SETCNTRLVALUE = B.SETCNTRLVALUE
AND SA.TREE_NAME = B.TREE_NAME
AND SA.EFFDT = B.EFFDT
AND SA.TREE_NODE_NUM = B.TREE_NODE_NUM

************************************************************************************************************************************************

===========================================================================================================================================================

创建HLS_DEPTTREE_VW 

view SQL 如下：

SELECT TD.SETID
, TD.EFFDT
, TN1.TREE_NODE
, TN1.TREE_LEVEL_NUM
, TN2.TREE_NODE PARENT_NODE
, TN2.TREE_LEVEL_NUM PARENT_NODE_LEVEL
FROM PSTREEDEFN TD
, PSTREENODE TN1
, PSTREENODE TN2
WHERE TD.TREE_NAME = 'DEPT_SECURITY'
AND TD.EFFDT = (
SELECT MAX(TD_.EFFDT)
FROM PSTREEDEFN TD_
WHERE TD_.SETID = TD.SETID
AND TD_.TREE_NAME = 'DEPT_SECURITY'
AND TD_.EFFDT <= %CurrentDateIn
AND TD_.EFF_STATUS = 'A')
AND TN1.SETID = TD.SETID
AND TN1.TREE_NAME = TD.TREE_NAME
AND TN1.EFFDT = TD.EFFDT
AND TN2.SETID = TD.SETID
AND TN2.TREE_NAME = TD.TREE_NAME
AND TN2.EFFDT = TD.EFFDT
AND TN1.TREE_NODE_NUM BETWEEN TN2.TREE_NODE_NUM AND TN2.TREE_NODE_NUM_END

查询结果每个节点的每个上级节点都会体现出来：

 所以包含子部门可以这样写

 查找父节点：

SELECT A.TREE_NODE
,A.TREE_LEVEL_NUM
FROM PSTREENODE A
,PSTREENODE B
WHERE B.SETID = A.SETID
AND B.SETCNTRLVALUE = A.SETCNTRLVALUE
AND B.TREE_NAME = A.TREE_NAME
AND B.EFFDT = A.EFFDT
AND A.TREE_NAME = 'DEPT_SECURITY'
AND A.EFFDT = (
SELECT MAX(A1.EFFDT)
FROM PSTREEDEFN A1
WHERE A1.SETID = A.SETID
AND A1.SETCNTRLVALUE = A.SETCNTRLVALUE
AND A1.TREE_NAME = A.TREE_NAME
AND A1.EFFDT <= %DateIn(:3))
AND B.TREE_NODE_NUM BETWEEN A.TREE_NODE_NUM AND A.TREE_NODE_NUM_END
AND B.SETID = :1
AND B.TREE_NODE = :2
ORDER BY A.TREE_LEVEL_NUM

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

生成树的代码// &session.GetTree();首先一步是把所有节点的上下级关系，顶级节点上级为空

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

/**递归函数：处理子节点*/
Function ProcessNodeChildren(&_parentNode As ApiObject)
Local ApiObject &childNode;
Local boolean &first;
Local string &oprId, &runCntlId, &childName;
Warning &_parentNode.NAME;
Local SQL &sql;
&sql = CreateSQL("SELECT NODE_NAME FROM PS_DC_ONE_TREE_NOD WHERE PARENT_NODE_NAME=:1 ORDER BY NODE_NAME DESC", &_parentNode.NAME, &childName);
While &sql.Fetch(&childName)
&childNode = &_parentNode.InsertChildNode(&childName);
ProcessNodeChildren(&childNode);
End-While;
&sql.Close();
End-Function;

Local ApiObject &session;
Local ApiObject &rootNode, &myTree;
Local ApiObject &lvlColl;
Local string &rootName, &setId, &userKeyValue, &treeName, &structName;
Local date &treeEffdt;

&setId = "BU999"; /*集团统一集合ID*/
&userKeyValue = "";
&treeName = "DEPT_SECURITY";
&structName = "DEPARTMENT";
&treeEffdt = %Date; /*默认创建树时间为当天*/

&session = %Session;

&myTree = &session.GetTree();