如下SQL需查找并统计年假未休,现报错ORA-00942,请协助修改，
并优化oracle如下SQL性能，提高执行效率和减少资源占用：
WITH EMP_BASE AS (
SELECT
EMP_CODE,
EMP_NAME,
ANNUAL_LEAVE,
YEAR,
DECODE(HPB.WORK_PLACE, 0, '一园', 1, '二园', 2, '三园','','NULL', HPB.WORK_PLACE) AS PLANT,
HCP.SF_LSHR_NO_GET(EMP_CODE, 14) AS DIV,
HCP.SF_LSHR_NO_GET(EMP_CODE, 15) AS DEPT_NAME,
HCP.SF_LSHR_NO_GET(EMP_CODE, 13) AS CLASS_NAME,
SUBSTR(HCP.SF_LSHR_NO_GET(EMP_CODE, 15), LENGTH(HCP.SF_LSHR_NO_GET(EMP_CODE, 15))-5) AS DEPT_NO,
HCP.SF_LSHR_NO_GET(EMP_CODE, 2) AS SEGMENT_NO,
HCP.SF_LSHR_NO_GET(EMP_CODE, 9) AS EMAIL_FIELD9,
HCP.SF_LSHR_NO_GET(EMP_CODE, 10) AS EMAIL_FIELD10,
HCP.SF_LSHR_NO_GET(EMP_CODE, 11) AS EMAIL_FIELD11
FROM HCP.v_clk_tz_annual_leave
JOIN HCP.TZ_EMP_IN HPB ON EMP_CODE=HPB.EMPCODE
WHERE ANNUAL_LEAVE > 0
AND YEAR = TO_NUMBER(TO_CHAR(SYSDATE, 'yyyy'))
AND ((TO_CHAR(SYSDATE, 'mm') IN ('04', '05', '06', '07', '08', '09') AND
TRUNC(SYSDATE) IN
(SELECT MAX(B.A)
FROM (SELECT TRUNC(SYSDATE, 'mm') + ROWNUM - 1 A
FROM DBA_OBJECTS
WHERE ROWNUM < 32) B
WHERE TO_CHAR(B.A, 'day') = 'friday ')) OR
(TO_CHAR(SYSDATE, 'mm') IN ('10', '11') AND
TO_CHAR(SYSDATE, 'Day') = 'Friday ') OR
TO_CHAR(SYSDATE, 'mm') = '12')
),
LOOKUP_PREP AS (
SELECT REVERSE2, MEANING, HCP.SF_LSHR_NO_GET(MEANING, 8) AS V_EMAIL
FROM HCP.KS_LOOKUP_VALUE
WHERE LOOKUP_TYPE = 'DEPT_PEOPLE'
AND ENABLED = 'Y'
),
DEPT_PEOPLE AS (
SELECT
E.EMP_CODE,
L.V_EMAIL,
ROW_NUMBER() OVER (
PARTITION BY E.EMP_CODE
ORDER BY CASE WHEN L.REVERSE2 = E.DEPT_NO THEN 1 ELSE 2 END
) AS rnk
FROM EMP_BASE E
JOIN LOOKUP_PREP L
ON L.REVERSE2 IN (E.DEPT_NO, E.SEGMENT_NO)
),
GROUPED_DATA AS (
SELECT
E.PLANT,
E.DIV,
E.DEPT_NAME,
E.CLASS_NAME,
COUNT(E.EMP_CODE) AS UNPEOPLE,
SUM(E.ANNUAL_LEAVE) AS UNDAY,
MAX(D.V_EMAIL) || ';' || HCP.SF_LSHR_NO_GET(MAX(E.EMAIL_FIELD9), 8) AS TOEMAIL,
HCP.SF_LSHR_NO_GET(MAX(E.EMAIL_FIELD10), 8) || ';' ||
HCP.SF_LSHR_NO_GET(MAX(E.EMAIL_FIELD11), 8) AS CCEMAIL
FROM EMP_BASE E
JOIN DEPT_PEOPLE D
ON E.EMP_CODE = D.EMP_CODE AND D.rnk = 1
GROUP BY E.PLANT, E.DIV, E.DEPT_NAME, E.CLASS_NAME
)
SELECT PLANT 厂区,
DIV 处级,
DEPT_NAME 部门名称,
CLASS_NAME 课别名称,
UNPEOPLE 未休人数,
UNDAY 未休天数,
TOEMAIL,
CCEMAIL
FROM(SELECT
PLANT,
DIV,
DEPT_NAME,
CLASS_NAME,
UNPEOPLE,
UNDAY,
TOEMAIL,
CCEMAIL
FROM GROUPED_DATA
UNION ALL
SELECT
'合计',
'',
'',
'',
COUNT(EMP_CODE),
SUM(ANNUAL_LEAVE),
'',
''
FROM EMP_BASE
WHERE ANNUAL_LEAVE > 0)
WHERE UNDAY IS NOT NULL
ORDER BY PLANT, DIV, DEPT_NAME, CLASS_NAME
扫一扫
4776
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
