子节点，子部门的写法

这里用到了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

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

SELECT P2.*
FROM
(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)) P,
PSTREENODE P1 ,
PSTREELEAF P2
WHERE P1.SETID = P.SETID
AND P1.SETCNTRLVALUE = P.SETCNTRLVALUE
AND P1.TREE_NAME = P.TREE_NAME
AND P1.EFFDT = P.EFFDT
AND P1.TREE_NODE = 'COST_CTR_10053'
AND P2.SETID = P1.SETID
AND P2.SETCNTRLVALUE = P1.SETCNTRLVALUE
AND P2.TREE_NAME = P1.TREE_NAME
AND P2.EFFDT = P1.EFFDT
-- AND P2.TREE_NODE_NUM BETWEEN P1.TREE_NODE_NUM AND P1.TREE_NODE_NUM_END
AND P2.TREE_NODE_NUM = P1.TREE_NODE_NUM

AND (
( NVL(LENGTH(REPLACE(TRANSLATE('143','0123456789.',' '),' ','')), 0) <> 0 AND '143' BETWEEN P2.RANGE_FROM AND P2.RANGE_TO)
OR
( NVL(LENGTH(REPLACE(TRANSLATE('143','0123456789.',' '),' ','')), 0) = 0 AND '143' BETWEEN P2.RANGE_FROM AND P2.RANGE_TO AND LENGTH('143') BETWEEN LENGTH(P2.RANGE_FROM) AND LENGTH(P2.RANGE_TO))
)

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

创建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();

If All(&myTree) Then
/*删除当前生效日期的树定义*/
Local date &cur_treeEffdt;
SQLExec(SQL.DC_GET_MAXDT_TREE_SQL, &setId, &cur_treeEffdt);
&treeReturn = &myTree.Delete(&setId, &userKeyValue, &treeName, &cur_treeEffdt, "");

&treeReturn = &myTree.Create(&setId, &userKeyValue, &treeName, &treeEffdt, &structName);

If &treeReturn <> 0 Then
Error MsgGet(30009, 231, "创建树失败！");
End-If;

&myTree.description = "集团统一部门树";

/* add level */
&lvlColl = &myTree.levels;
&LEVEL = &lvlColl.add("1");
&LEVEL.description = "LEVEL 1";

/* add root node:父节点为空的节点为跟节点 */
SQLExec("SELECT NODE_NAME FROM PS_DC_ONE_TREE_NOD WHERE PARENT_NODE_NAME=' '", &rootName);

/*添加跟节点*/
&rootNode = &myTree.insertroot(&rootName);

/*处理子节点*/
ProcessNodeChildren(&rootNode);

/*保存树*/
&RSLT = &myTree.Save();

End-If;