测试SQL语句查询

WITH ARCH_TASK AS
(SELECT TSK.TASK_ID TASKID,
         TSK.PROCESS_ID PROCESSID,
         TSK.PROCESS_CODE PROCESSCODE,
         TSK.PROCESS_VERSION PROCESSVERSION,
         TSK.INSTANCE_ID INSTANCEID,
         TSK.INSTANCE_CODE INSTANCECODE,
         TSK.ACT_ID ACTID,
         TSK.ACT_CODE ACTCODE,
         TSK.ACT_NAME ACTNAME,
         TSK.TASK_TITLE TASKTITLE,
         TSK.FROM_USER FROMUSER,
         (SELECT B.EMP_NAME
            FROM XIP_PUB_USERS A, XIP_PUB_EMPS B
           WHERE A.EMP_ID = B.EMP_ID
             AND A.USER_ID = TSK.FROM_USER) FROMUSERNAME,
         TSK.ASSIGN_USER ASSIGNUSER,
         (SELECT B.EMP_NAME
            FROM XIP_PUB_USERS A, XIP_PUB_EMPS B
           WHERE A.EMP_ID = B.EMP_ID
             AND A.USER_ID = TSK.ASSIGN_USER) ASSIGNUSERNAME,
         TSK.EXECUTE_USER EXECUTEUSER,
         (SELECT B.EMP_NAME
            FROM XIP_PUB_USERS A, XIP_PUB_EMPS B
           WHERE A.EMP_ID = B.EMP_ID
             AND A.USER_ID = TSK.EXECUTE_USER) EXECUTEUSERNAME,
         (SELECT B.EMP_CODE
            FROM XIP_PUB_USERS A, XIP_PUB_EMPS B
           WHERE A.EMP_ID = B.EMP_ID
             AND A.USER_ID = TSK.EXECUTE_USER) EXECUTEUSERCODE,
         TSK.TASK_STATE TASKSTATE,
         TSK.BEGIN_DATE BEGINDATE,
         TSK.END_DATE ENDDATE,
         TSK.RESULT RESULT,
         TSK.APPROVE_COMMENT APPROVECOMMNET
    FROM XIP_WF_ARCH_TASK /*归档*/ TSK
   WHERE TSK.INSTANCE_ID IN
         (SELECT X.INSTANCE_ID
            FROM XIP_WF_ARCH_INSTANCE X, XSR_XZ_EX_PAY_REQ_H H
           WHERE X.INSTANCE_CODE = H.INS_CODE
             AND H.PAY_REQ_H_ID = ?)
     AND TSK.CREATE_TYPE != 'copyto'
     AND TSK.TASK_EFFECTIVE = 'Y'),
INS_TASK AS
(SELECT TSK.TASK_ID TASKID,
         TSK.PROCESS_ID PROCESSID,
         TSK.PROCESS_CODE PROCESSCODE,
         TSK.PROCESS_VERSION PROCESSVERSION,
         TSK.INSTANCE_ID INSTANCEID,
         TSK.INSTANCE_CODE INSTANCECODE,
         TSK.ACT_ID ACTID,
         TSK.ACT_CODE ACTCODE,
         TSK.ACT_NAME ACTNAME,
         TSK.TASK_TITLE TASKTITLE,
         TSK.FROM_USER FROMUSER,
         (SELECT B.EMP_NAME
            FROM XIP_PUB_USERS A, XIP_PUB_EMPS B
           WHERE A.EMP_ID = B.EMP_ID
             AND A.USER_ID = TSK.FROM_USER) FROMUSERNAME,
         TSK.ASSIGN_USER ASSIGNUSER,
         (SELECT B.EMP_NAME
            FROM XIP_PUB_USERS A, XIP_PUB_EMPS B
           WHERE A.EMP_ID = B.EMP_ID
             AND A.USER_ID = TSK.ASSIGN_USER) ASSIGNUSERNAME,
         TSK.EXECUTE_USER EXECUTEUSER,
         (SELECT B.EMP_NAME
            FROM XIP_PUB_USERS A, XIP_PUB_EMPS B
           WHERE A.EMP_ID = B.EMP_ID
             AND A.USER_ID = TSK.EXECUTE_USER) EXECUTEUSERNAME,
         (SELECT B.EMP_CODE
            FROM XIP_PUB_USERS A, XIP_PUB_EMPS B
           WHERE A.EMP_ID = B.EMP_ID
             AND A.USER_ID = TSK.EXECUTE_USER) EXECUTEUSERCODE,
         TSK.TASK_STATE TASKSTATE,
         TSK.BEGIN_DATE BEGINDATE,
         TSK.END_DATE ENDDATE,
         TSK.RESULT RESULT,
         TSK.APPROVE_COMMENT APPROVECOMMNET
    FROM XIP_WF_INS_TASK /*待办*/ TSK
   WHERE TSK.INSTANCE_ID IN
         (SELECT X.INSTANCE_ID
            FROM XIP_WF_PROCESS_INSTANCE X, XSR_XZ_EX_PAY_REQ_H H
           WHERE X.INSTANCE_CODE = H.INS_CODE
             AND H.PAY_REQ_H_ID = ?)
     AND TSK.CREATE_TYPE != 'copyto'
     AND TSK.TASK_EFFECTIVE = 'Y'
     AND TSK.TASK_STATE <> 'open')
/*SELECT * FROM ARCH_TASK
UNION ALL
SELECT *
  FROM INS_TASK*/

SELECT TA.TASKID,
       TA.ACTID,
       TA.APPROVECOMMNET /*意见*/,
       TA.EXECUTEUSERNAME /*待办执行人*/,
       './../reportFiles/PIC/' || TA.EXECUTEUSERCODE || '.JPG' EXECUTEUSERCODE,
       TA.ACTNAME /*审批阶段*/,
       TA.BEGINDATE,
       TA.ENDDATE
  FROM ARCH_TASK TA
UNION ALL
SELECT IT.TASKID,
       IT.ACTID,
       IT.APPROVECOMMNET /*意见*/,
       IT.EXECUTEUSERNAME /*待办执行人*/,
       './../reportFiles/PIC/' || IT.EXECUTEUSERCODE || '.JPG' EXECUTEUSERCODE,
       IT.ACTNAME /*审批阶段*/,
       IT.BEGINDATE,
       IT.ENDDATE
  FROM INS_TASK IT