CODEWITHSUNDEEP
sqloracle-database
SQL Novice
SQL Novice

Reputation: 323

SQL Query Display on Case Statement

I am running the below SQL and need to display only those rows when Alert is YES. The query is returning an error.

SELECT
    COMP,
    TO_CHAR(PERIOD_START, 'DAY') DOW,
    trunc(PERIOD_START) Period_Date,
    round((sum(EC) / sum(mon)) * 100, 2) Pct_EC,
    CASE WHEN COMP = 2
    AND round((sum(EC) / sum(mon)) * 100, 2) >= 2.96 THEN 'YES' WHEN COMP = 3
    AND round((sum(EC) / sum(mon)) * 100, 2) >= 1.62 THEN 'YES' WHEN COMP = 4
    AND round((sum(EC) / sum(mon)) * 100, 2) >= 2.06 THEN 'YES' ELSE 'NO' END as ALERT
FROM
    KPI
WHERE
    ALERT = 'YES'
    AND trunc(period_start) >= trunc(sysdate -7)
    and trunc(period_start) < trunc(sysdate)
GROUP BY
    trunc(period_start),
    TO_CHAR(PERIOD_START, 'DAY'),
    COMP,
    trunc(PERIOD_START)
ORDER BY
    trunc(period_start) desc,
    COMP,
    trunc(PERIOD_START)

Output Only display highlighted record.

enter image description here

Any help will be greatly appreciated.

Upvotes: 0

Views: 63

Answers (2)

Turo
Turo

Reputation: 4924

Here another approach with subquery, sorting/grouping after trunc(PERIOD_START) only once and decode to get rid of 7 times the sum/round etc :

I assume the subselect is appropriate here since the result set will be not to big...

SELECT COMP, DOW, Period_date, pct_ec, 'YES' as alert
FROM
(SELECT
    COMP,
    TO_CHAR(PERIOD_START, 'DAY') DOW,
    trunc(PERIOD_START) Period_Date,
    round((sum(EC) / sum(mon)) * 100, 2) Pct_EC
FROM
    KPI
WHERE
    trunc(period_start) >= trunc(sysdate -7)
    and trunc(period_start) < trunc(sysdate)
GROUP BY
    trunc(period_start),
    TO_CHAR(PERIOD_START, 'DAY'),
    COMP ) temp
WHERE 
   COMP in (2,3,4) 
       AND pct_ec >= decode( COMP, 2, 2.96, 3, 1.62, 4, 2.06, 0)   
ORDER BY
    period_date desc,
    COMP

db-fiddle

Upvotes: 1

eshirvana
eshirvana

Reputation: 24613

filter them out using having:

SELECT
    COMP,
    TO_CHAR(PERIOD_START, 'DAY') DOW,
    trunc(PERIOD_START) Period_Date,
    round((sum(EC) / sum(mon)) * 100, 2) Pct_EC,
    CASE WHEN COMP = 2
    AND round((sum(EC) / sum(mon)) * 100, 2) >= 2.96 THEN 'YES' WHEN COMP = 3
    AND round((sum(EC) / sum(mon)) * 100, 2) >= 1.62 THEN 'YES' WHEN COMP = 4
    AND round((sum(EC) / sum(mon)) * 100, 2) >= 2.06 THEN 'YES' ELSE 'NO' END as ALERT
FROM
    KPI
WHERE
    trunc(period_start) >= trunc(sysdate -7)
    and trunc(period_start) < trunc(sysdate)
GROUP BY
    trunc(period_start),
    TO_CHAR(PERIOD_START, 'DAY'),
    COMP,
    trunc(PERIOD_START)
HAVING CASE WHEN COMP = 2
    AND round((sum(EC) / sum(mon)) * 100, 2) >= 2.96 THEN 'YES' WHEN COMP = 3
    AND round((sum(EC) / sum(mon)) * 100, 2) >= 1.62 THEN 'YES' WHEN COMP = 4
    AND round((sum(EC) / sum(mon)) * 100, 2) >= 2.06 THEN 'YES' ELSE 'NO' END = 'YES'
ORDER BY
    trunc(period_start) desc,
    COMP,
    trunc(PERIOD_START)

Upvotes: 1

Related Questions