SQL Developer — оператор CASE с критериями

Попытка написать инструкцию sql для Peoplesoft.

Мое текущее утверждение «работает», но вывод неверен. Вот sql:

SELECT
A.EMPLID, 
A.DEPTID, 
A.LOCATION, 
A.PAYGROUP, 
A.STD_HOURS, 
A.ANNUAL_RT, 
A.COMPRATE, 
SUM(case b.comp_ratecd when 'NAANNL' then b.comprate  else 0 end) AS NAANNL,
sum(case b.comp_ratecd when 'NAHRLY' then b.comprate else 0 end) as NAHRLY,
sum(case C.ERNCD when '007' then (C.HOURLY_RT + C.OTH_PAY) else 0 end) as BASEFTO,
sum(case C.ERNCD when '013' then (C.HOURLY_RT + C.OTH_PAY) else 0 end) as DEFCOMP
FROM PS_JOB A
    ,PS_COMPENSATION B
    ,ps_addl_pay_data C
WHERE A.EFFDT = 
(SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED 
 WHERE A.EMPLID = A_ED.EMPLID 
 AND A.EMPL_RCD = A_ED.EMPL_RCD 
 AND A_ED.EFFDT <= SYSDATE) 
AND A.EFFSEQ = 
(SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES 
 WHERE A.EMPLID = A_ES.EMPLID 
 AND A.EMPL_RCD = A_ES.EMPL_RCD 
 AND A.EFFDT = A_ES.EFFDT) 
AND A.EMPL_STATUS = 'A' 
AND A.EMPLID = B.EMPLID 
AND A.EMPL_RCD = B.EMPL_RCD 
AND A.EFFSEQ = B.EFFSEQ 
AND B.EFFDT = A.EFFDT 
AND A.EMPLID = C.EMPLID (+)
AND A.EMPL_RCD = C.EMPL_RCD (+)
group by A.EMPLID , A.DEPTID, A.LOCATION, A.PAYGROUP, A.STD_HOURS, A.ANNUAL_RT, A.COMPRATE
ORDER BY 1 ;

Проблема заключается в том, что при наличии 2 или более строк в c.addl_pay_data сумма в столбцах NAANNL и NAHRLY умножается на количество строк в addl_pay_data.

Я был бы признателен за любую помощь, которая подскажет мне, как написать это так, чтобы, когда счетчик erncd от addl_pay_data > 0, затем взять comprate от компенсации и разделить на счетчик erncd. Если count = 0, то просто перечислите сравнение с компенсацией.

Спасибо за любую помощь, которую вы можете предоставить.


sql case peoplesoft
person user3663039    schedule 22.05.2014    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Вы пытаетесь суммировать две разные таблицы по соединению. Вместо этого вы должны выполнить агрегирование для каждой дочерней таблицы отдельно в подзапросе, а затем объединить результат:

SELECT
    A.EMPLID, 
    A.DEPTID, 
    A.LOCATION, 
    A.PAYGROUP, 
    A.STD_HOURS, 
    A.ANNUAL_RT, 
    A.COMPRATE, 
    b.NAANNL, b.NAHRLY, c.BASEFTO, c.DEFCOMP
FROM PS_JOB A
LEFT OUTER JOIN (
    SELECT SUM(case b.comp_ratecd when 'NAANNL' then b.comprate  else 0 end) AS NAANNL,
       sum(case b.comp_ratecd when 'NAHRLY' then b.comprate else 0 end) as NAHRLY,
       EFFDT, EMPL_RCD, EFFSEQ, EMPLID
    FROM PS_COMPENSATION B
    GROUP BY EFFDT, EMPL_RCD, EFFSEQ, EMPLID
) AS B
ON A.EMPLID = B.EMPLID 
AND A.EMPL_RCD = B.EMPL_RCD 
AND A.EFFSEQ = B.EFFSEQ 
AND B.EFFDT = A.EFFDT 
LEFT OUTER JOIN (
    SELECT sum(case C.ERNCD when '007' then (C.HOURLY_RT + C.OTH_PAY) else 0 end) as BASEFTO,
        sum(case C.ERNCD when '013' then (C.HOURLY_RT + C.OTH_PAY) else 0 end) as DEFCOMP,
        EMPLID, EMPL_RCD
    FROM ps_addl_pay_data C
    GROUP BY EMPLID, EMPL_RCD
) AS C
ON A.EMPLID = C.EMPLID (+)
AND A.EMPL_RCD = C.EMPL_RCD (+)
WHERE A.EFFDT = 
(SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED 
 WHERE A.EMPLID = A_ED.EMPLID 
 AND A.EMPL_RCD = A_ED.EMPL_RCD 
 AND A_ED.EFFDT <= SYSDATE) 
AND A.EFFSEQ = 
(SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES 
 WHERE A.EMPLID = A_ES.EMPLID 
 AND A.EMPL_RCD = A_ES.EMPL_RCD 
 AND A.EFFDT = A_ES.EFFDT) 
AND A.EMPL_STATUS = 'A' 
group by A.EMPLID , A.DEPTID, A.LOCATION, A.PAYGROUP, A.STD_HOURS, A.ANNUAL_RT, A.COMPRATE
ORDER BY 1 ;
person lc.    schedule 22.05.2014