EAPUserPrivilege-db2.xml
<!--根据用户角色权限查看相应的模块 -->
<statement id="AD_PROGRAM_TREE" resultClass="EAPTreeNodeMap" remapResults="true">
<![CDATA[
WITH RPL (ID,PCODE, PNAME, START_PROGRAM,LEAF, PID, TAXIS_NO) AS
(
SELECT ROOT.ROW_ID AS ID, ROOT.PROGRAM_CODE AS PCODE, ROOT.PROGRAM_NAME AS PNAME, ROOT.START_PROGRAM,CASE ROOT.LEAF_YN WHEN 'Y' THEN 'true' WHEN 'N' THEN 'false' END AS LEAF,ROOT.PARENT_MODULE_ID AS PID,ROOT.TAXIS_NO
FROM AD_PROGRAM ROOT
WHERE ROOT.PARENT_MODULE_ID =#rowId# AND ROOT.USING_FLAG = '0' AND ROOT.VISIBLE_FLAG = '0' AND ROOT.LEVEL_NUM >=#fromLevelNum# AND ROOT.LEVEL_NUM <=#toLevelNum#
AND
(
ROOT.PROGRAM_CODE IN
(
SELECT DISTINCT LIMIT_NAME FROM OSM_LIMIT WHERE DELETED_FLAG = '0' AND OSM_LIMIT.IS_NO='Y'
AND ROLE_ID IN
(
SELECT ROLE_ID FROM OSM_ROLE,OSM_CONNECT,OSM_PEOPLE WHERE OSM_ROLE.DELETED_FLAG = '0' AND OSM_CONNECT.DELETED_FLAG = '0' AND OSM_PEOPLE.DELETED_FLAG = '0' AND OSM_ROLE.ROW_ID=OSM_CONNECT.ROLE_ID AND OSM_CONNECT.PEOPLE_ID=OSM_PEOPLE.ROW_ID AND OSM_PEOPLE.ROW_ID = #USER_ID#
)
)
AND ROOT.CONTROL_FLAG = '1'
OR
ROOT.CONTROL_FLAG='0'
)
UNION ALL
SELECT CHILD.ROW_ID AS ID, CHILD.PROGRAM_CODE AS PCODE, CHILD.PROGRAM_NAME AS PNAME,CHILD.START_PROGRAM,CASE CHILD.LEAF_YN WHEN 'Y' THEN 'true' WHEN 'N' THEN 'false' END AS LEAF,CHILD.PARENT_MODULE_ID AS PID,CHILD.TAXIS_NO
FROM RPL PARENT, AD_PROGRAM CHILD
WHERE PARENT.ID = CHILD.PARENT_MODULE_ID
AND CHILD.USING_FLAG = '0' AND CHILD.VISIBLE_FLAG = '0' AND CHILD.LEVEL_NUM >= #fromLevelNum# AND CHILD.LEVEL_NUM <=#toLevelNum#
AND
(
CHILD.ROW_ID IN
(
SELECT DISTINCT LIMIT_NAME FROM OSM_LIMIT WHERE DELETED_FLAG = '0' AND OSM_LIMIT.IS_NO='Y'
AND ROLE_ID IN
(
SELECT ROLE_ID FROM OSM_ROLE,OSM_CONNECT,OSM_PEOPLE WHERE OSM_ROLE.DELETED_FLAG = '0' AND OSM_CONNECT.DELETED_FLAG = '0' AND OSM_PEOPLE.DELETED_FLAG = '0' AND OSM_ROLE.ROW_ID=OSM_CONNECT.ROLE_ID AND OSM_CONNECT.PEOPLE_ID=OSM_PEOPLE.ROW_ID AND OSM_PEOPLE.ROW_ID = #USER_ID#
)
)
AND CHILD.CONTROL_FLAG = '1'
OR
CHILD.CONTROL_FLAG='0'
)
)
SELECT DISTINCT ID, PCODE, PNAME, START_PROGRAM, LEAF, PID, TAXIS_NO
FROM RPL
ORDER BY TAXIS_NO
]]>
</statement>
错误信息:
[2012-04-24 11:28:16] DEBUG >> ajax.EAPAjaxServlet >>
[2012-04-24 11:28:16] DEBUG >> sql.Connection >>{conn-100012} Connection
[2012-04-24 11:28:16] DEBUG >> sql.Connection >>{conn-100012} Preparing Statement: WITH RPL (ID,PCODE, PNAME, START_PROGRAM,LEAF, PID, TAXIS_NO) AS ( SELECT ROOT.ROW_ID AS ID, ROOT.PROGRAM_CODE AS PCODE, ROOT.PROGRAM_NAME AS PNAME, ROOT.START_PROGRAM,CASE ROOT.LEAF_YN WHEN 'Y' THEN 'true' WHEN 'N' THEN 'false' END AS LEAF,ROOT.PARENT_MODULE_ID AS PID,ROOT.TAXIS_NO FROM AD_PROGRAM ROOT WHERE ROOT.PARENT_MODULE_ID =? AND ROOT.USING_FLAG = '0' AND ROOT.VISIBLE_FLAG = '0' AND ROOT.LEVEL_NUM >=? AND ROOT.LEVEL_NUM <=? AND ( ROOT.PROGRAM_CODE IN ( SELECT DISTINCT LIMIT_NAME FROM OSM_LIMIT WHERE DELETED_FLAG = '0' AND OSM_LIMIT.IS_NO='Y' AND ROLE_ID IN ( SELECT ROLE_ID FROM OSM_ROLE,OSM_CONNECT,OSM_PEOPLE WHERE OSM_ROLE.DELETED_FLAG = '0' AND OSM_CONNECT.DELETED_FLAG = '0' AND OSM_PEOPLE.DELETED_FLAG = '0' AND OSM_ROLE.ROW_ID=OSM_CONNECT.ROLE_ID AND OSM_CONNECT.PEOPLE_ID=OSM_PEOPLE.ROW_ID AND OSM_PEOPLE.ROW_ID = ? ) ) AND ROOT.CONTROL_FLAG = '1' OR ROOT.CONTROL_FLAG='0' ) UNION ALL SELECT CHILD.ROW_ID AS ID, CHILD.PROGRAM_CODE AS PCODE, CHILD.PROGRAM_NAME AS PNAME,CHILD.START_PROGRAM,CASE CHILD.LEAF_YN WHEN 'Y' THEN 'true' WHEN 'N' THEN 'false' END AS LEAF,CHILD.PARENT_MODULE_ID AS PID,CHILD.TAXIS_NO FROM RPL PARENT, AD_PROGRAM CHILD WHERE PARENT.ID = CHILD.PARENT_MODULE_ID AND CHILD.USING_FLAG = '0' AND CHILD.VISIBLE_FLAG = '0' AND CHILD.LEVEL_NUM >= ? AND CHILD.LEVEL_NUM <=? AND ( CHILD.ROW_ID IN ( SELECT DISTINCT LIMIT_NAME FROM OSM_LIMIT WHERE DELETED_FLAG = '0' AND OSM_LIMIT.IS_NO='Y' AND ROLE_ID IN ( SELECT ROLE_ID FROM OSM_ROLE,OSM_CONNECT,OSM_PEOPLE WHERE OSM_ROLE.DELETED_FLAG = '0' AND OSM_CONNECT.DELETED_FLAG = '0' AND OSM_PEOPLE.DELETED_FLAG = '0' AND OSM_ROLE.ROW_ID=OSM_CONNECT.ROLE_ID AND OSM_CONNECT.PEOPLE_ID=OSM_PEOPLE.ROW_ID AND OSM_PEOPLE.ROW_ID = ? ) ) AND CHILD.CONTROL_FLAG = '1' OR CHILD.CONTROL_FLAG='0' ) ) SELECT DISTINCT ID, PCODE, PNAME, START_PROGRAM, LEAF, PID, TAXIS_NO FROM RPL ORDER BY TAXIS_NO
[2012-04-24 11:28:16] DEBUG >> sql.PreparedStatement >>{pstm-100013} Executing Statement: WITH RPL (ID,PCODE, PNAME, START_PROGRAM,LEAF, PID, TAXIS_NO) AS ( SELECT ROOT.ROW_ID AS ID, ROOT.PROGRAM_CODE AS PCODE, ROOT.PROGRAM_NAME AS PNAME, ROOT.START_PROGRAM,CASE ROOT.LEAF_YN WHEN 'Y' THEN 'true' WHEN 'N' THEN 'false' END AS LEAF,ROOT.PARENT_MODULE_ID AS PID,ROOT.TAXIS_NO FROM AD_PROGRAM ROOT WHERE ROOT.PARENT_MODULE_ID =? AND ROOT.USING_FLAG = '0' AND ROOT.VISIBLE_FLAG = '0' AND ROOT.LEVEL_NUM >=? AND ROOT.LEVEL_NUM <=? AND ( ROOT.PROGRAM_CODE IN ( SELECT DISTINCT LIMIT_NAME FROM OSM_LIMIT WHERE DELETED_FLAG = '0' AND OSM_LIMIT.IS_NO='Y' AND ROLE_ID IN ( SELECT ROLE_ID FROM OSM_ROLE,OSM_CONNECT,OSM_PEOPLE WHERE OSM_ROLE.DELETED_FLAG = '0' AND OSM_CONNECT.DELETED_FLAG = '0' AND OSM_PEOPLE.DELETED_FLAG = '0' AND OSM_ROLE.ROW_ID=OSM_CONNECT.ROLE_ID AND OSM_CONNECT.PEOPLE_ID=OSM_PEOPLE.ROW_ID AND OSM_PEOPLE.ROW_ID = ? ) ) AND ROOT.CONTROL_FLAG = '1' OR ROOT.CONTROL_FLAG='0' ) UNION ALL SELECT CHILD.ROW_ID AS ID, CHILD.PROGRAM_CODE AS PCODE, CHILD.PROGRAM_NAME AS PNAME,CHILD.START_PROGRAM,CASE CHILD.LEAF_YN WHEN 'Y' THEN 'true' WHEN 'N' THEN 'false' END AS LEAF,CHILD.PARENT_MODULE_ID AS PID,CHILD.TAXIS_NO FROM RPL PARENT, AD_PROGRAM CHILD WHERE PARENT.ID = CHILD.PARENT_MODULE_ID AND CHILD.USING_FLAG = '0' AND CHILD.VISIBLE_FLAG = '0' AND CHILD.LEVEL_NUM >= ? AND CHILD.LEVEL_NUM <=? AND ( CHILD.ROW_ID IN ( SELECT DISTINCT LIMIT_NAME FROM OSM_LIMIT WHERE DELETED_FLAG = '0' AND OSM_LIMIT.IS_NO='Y' AND ROLE_ID IN ( SELECT ROLE_ID FROM OSM_ROLE,OSM_CONNECT,OSM_PEOPLE WHERE OSM_ROLE.DELETED_FLAG = '0' AND OSM_CONNECT.DELETED_FLAG = '0' AND OSM_PEOPLE.DELETED_FLAG = '0' AND OSM_ROLE.ROW_ID=OSM_CONNECT.ROLE_ID AND OSM_CONNECT.PEOPLE_ID=OSM_PEOPLE.ROW_ID AND OSM_PEOPLE.ROW_ID = ? ) ) AND CHILD.CONTROL_FLAG = '1' OR CHILD.CONTROL_FLAG='0' ) ) SELECT DISTINCT ID, PCODE, PNAME, START_PROGRAM, LEAF, PID, TAXIS_NO FROM RPL ORDER BY TAXIS_NO
[2012-04-24 11:28:16] DEBUG >> sql.PreparedStatement >>{pstm-100013} Parameters: [70, 2, 10, 0, 2, 10, 0]
[2012-04-24 11:28:16] DEBUG >> sql.PreparedStatement >>{pstm-100013} Types: [java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String]
[2012-04-24 11:28:16] ERROR >> tree.EAPTreeUtil >>com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/neusoft/ermsuite/platform/common/sm/privilege/userPrivilege/dao/EAPUserPrivilege-db2.xml.
--- The error occurred while applying a parameter map.
--- Check the common.sm.privilege.userPrivilege.UserPrivilege.AD_PROGRAM_TREE-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: com.ibm.db2.jcc.b.nm: DB2 SQL Error: SQLCODE=-401, SQLSTATE=42818, SQLERRMC=IN, DRIVER=3.50.152