user5511576 user5511576 - 1 month ago 6
SQL Question

ORACLE sum inside a case statement

Hi I need the result of this. so if a entityID matches to a value I need the sum of certain column.I am getting an expression missing error. Can someone point me to where the error is?
Thanks.

SELECT
p.jobTitle,
p.department,
p.person,
ufr.meets,
ufr.exceeds,
CASE
WHEN ufr.entityid = 'AHT' THEN (AD.acdcalls + AD.daacdcalls)
WHEN ufr.entityid = 'ACW' THEN (AD.acdcalls + AD.daacdcalls)
WHEN ufr.entityid = 'Adherence' THEN SUM(AA.totalSched)
WHEN ufr.entityid = 'Conformance' THEN SUM(AS.minutes)
ELSE null
END as weight,
(weight * meets) AS weightedMeets,
(weight * exceeds) AS weightedExceeds

FROM M_PERSON p
JOIN A_TMP5408_UNFLTRDRESULTSAG ufr
ON ufr.department = p.department AND ufr.jobTitle = p.jobTitle
LEFT JOIN M_AvayaDAgentChunk AD
ON AD.person = p.person and ufr.split = AD.split
LEFT JOIN M_AgentAdherenceChunk AA
ON AA.person = p.person
LEFT JOIN M_AgentScheduleChunk AS
ON AS.person = p.person

GROUP BY
p.person,
p.department,
p.jobTitle,
ufr.meets,
ufr.exceeds,
weight,
weightedMeets,
weightedExceeds

Answer

As well as the issues mentioned by @GordonLinoff (that AS is a keyword) and @DCookie (you need entityid in the group-by):

  • you also need acdcalls and daacdcalls in the group-by (unless you can aggregate those);
  • you can't refer to a column alias in the same level of query, so (weight * meets) AS weightedMeets isn't allowed - you've just define what weight is, in the same select list. You need to use an inline view, or a CTE, if you don't want to repeat the case logic.

I think this does what you want:

SELECT
        jobTitle,
        department,
        person,
        meets,
        exceeds,
        weight,  
        (weight * meets) AS weightedMeets,
        (weight * exceeds) AS weightedExceeds
FROM
(
        SELECT                               
                MP.jobTitle,
                MP.department,
                MP.person,
                ufr.meets,
                ufr.exceeds,
                CASE 
                     WHEN ufr.entityid = 'AHT' THEN (MADAC.acdcalls + MADAC.daacdcalls) 
                     WHEN ufr.entityid = 'ACW' THEN (MADAC.acdcalls + MADAC.daacdcalls) 
                     WHEN ufr.entityid = 'Adherence' THEN SUM(MAAC.totalSched)
                     WHEN ufr.entityid = 'Conformance' THEN SUM(MASC.minutes)
                     ELSE null
                END as weight
        FROM  M_PERSON MP
        JOIN A_TMP5408_UNFLTRDRESULTSAG ufr
                ON ufr.department = MP.department AND ufr.jobTitle = MP.jobTitle 
        LEFT JOIN M_AvayaDAgentChunk MADAC 
                ON MADAC.person = MP.person and ufr.split = MADAC.split    
        LEFT JOIN M_AgentAdherenceChunk MAAC
                ON MAAC.person = MP.person 
        LEFT JOIN M_AgentScheduleChunk MASC
                ON MASC.person = MP.person 
        GROUP BY
                MP.person,
                MP.department,
                MP.jobTitle,
                ufr.meets,
                ufr.exceeds,
                ufr.entityid,
                MADAC.acdcalls,
                MADAC.daacdcalls
);

Your fist two case branches could be combined since the calculation is the same, but will work either way.