Count condition in CASE clause

Question

I am trying to get a conditional values based on count of error codes and their occurrences in specific time period from SQL query, but this query that I have written returns the first satisfied condition for all records.

What am I doing wrong

SELECT ERROR_CODE,
       CASE
         WHEN (SELECT Count(ERROR_CODE)
               FROM   ERROR_LOG
               WHERE  CRET_DTIM > Trunc(SYSDATE - 2)
                      AND ERROR_CODE = '50001') > 50 THEN 'RED'
         WHEN (SELECT Count(ERROR_CODE)
               FROM   ERROR_LOG
               WHERE  CRET_DTIM > Trunc(SYSDATE - 2)
                      AND ERROR_CODE = '50001') < 50 THEN 'GREEN'
         WHEN (SELECT Count(ERROR_CODE)
               FROM   ERROR_LOG
               WHERE  CRET_DTIM > Trunc(SYSDATE - 1)
                      AND ERROR_CODE IS NULL) > 100 THEN 'RED'
         WHEN (SELECT Count(ERROR_CODE)
               FROM   ERROR_LOG
               WHERE  CRET_DTIM > Trunc(SYSDATE - 1)
                      AND ERROR_CODE IS NULL) < 100 THEN 'GREEN'
       END AS ALERT_COLOR
FROM   ERROR_LOG
GROUP  BY ERROR_CODE

SAMPLE DATA

50005   05-JAN-15 11.05.51.570000000 AM
50001   05-JAN-15 02.39.57.840000000 PM
50001   05-JAN-15 02.31.06.700000000 PM
50001   05-JAN-15 02.21.49.350000000 PM
50001   05-JAN-15 01.59.13.910000000 PM
50001   05-JAN-15 01.50.05.900000000 PM
50001   05-JAN-15 01.30.19.270000000 PM
50001   05-JAN-15 01.11.10.510000000 PM
50001   05-JAN-15 12.00.00.720000000 PM
        05-JAN-15 09.42.10.670000000 AM
        05-JAN-15 09.37.31.590000000 AM

DESIRED OUPUT (change the count condition to see the desired output) COUNT

        2
 50001  8
 50005  1

SQL

SELECT ERROR_CODE,
       CASE
         WHEN (SELECT Count(ERROR_CODE)
               FROM   ERROR_LOG
               WHERE  CRET_DTIM > Trunc(SYSDATE - 2)
                      AND ERROR_CODE = '50001') between 5 and 100 THEN 'RED'
         WHEN (SELECT Count(ERROR_CODE)
               FROM   ERROR_LOG
               WHERE  CRET_DTIM > Trunc(SYSDATE - 2)
                      AND ERROR_CODE = '50001') < 5 THEN 'GREEN'
         WHEN (SELECT Count(ERROR_CODE)
               FROM   ERROR_LOG
               WHERE  CRET_DTIM > Trunc(SYSDATE - 1)
                      AND ERROR_CODE IS NULL) > 100 THEN 'RED'
         WHEN (SELECT Count(ERROR_CODE)
               FROM   ERROR_LOG
               WHERE  CRET_DTIM > Trunc(SYSDATE - 1)
                      AND ERROR_CODE IS NULL) between 5 and 100 THEN 'GREEN'
       END AS ALERT_COLOR
FROM   ERROR_LOG
GROUP  BY ERROR_CODE

Result

Error_code   Alert_color
             GREEN
50005        ''
50001        RED

I can see where the 2/8/1 counts are coming from, but how is that translating to GREEN/null/RED? With those numbers you'd get null/RED/null, since your case has no handler for codes 50005 or null, and the 50001 count of 8 hits the first (red) condition? — Alex Poole, Jan 06 '15 at 18:03

score 4 · Accepted Answer · answered Jan 06 '15 at 18:28

You're repeating the subqueries within each when clause for every row in the table, with no correlation. The subquery:

          (SELECT Count(ERROR_CODE)
           FROM   ERROR_LOG
           WHERE  CRET_DTIM > Trunc(SYSDATE - 2)
                  AND ERROR_CODE = '50001')

... is going to evaluate to 8 every time, for every row in the table; so regardless of the error code that's actually in that row, the first branch of the case will always match, so you see RED for every row.

Repeating the subquery is inefficient, even if you correlated it. Oracle will optimise/cache it but still... it would be better to generate your counts once:

select error_code,
  count(case when cret_dtim > trunc(sysdate - 2) then 1 end) as cnt_2_day,
  count(case when cret_dtim > trunc(sysdate - 1) then 1 end) as cnt_1_day
from error_log
where cret_dtim > trunc(sysdate - 2)
group by error_code
order by error_code nulls first;

ERROR_CODE CNT_2_DAY  CNT_1_DAY
---------- --------- ----------
(null)             2          2 
50001              8          8 
50005              1          1

All of your sample data is less than one day old, so the counts are the same; if you had data from two days ago they'd differ.

You can then use that - once - as an inner query (or CTE if you prefer) and use the results as input to another level of case to convert into your colour codes:

select error_code,
  case
    when error_code = '50001' and cnt_1_day between 5 and 100 then 'RED'
    when error_code = '50001' and cnt_2_day < 5 then 'GREEN'
    when error_code is null and cnt_1_day > 100 then 'RED'
    when error_code is null and cnt_1_day between 5 and 100 then 'GREEN'
    else null
  end as alert_colour
from 
(
  select error_code,
    count(case when cret_dtim > trunc(sysdate - 2) then 1 end) as cnt_2_day,
    count(case when cret_dtim > trunc(sysdate - 1) then 1 end) as cnt_1_day
  from error_log
  where cret_dtim > trunc(sysdate - 2)
  group by error_code
)
order by error_code nulls first;

ERROR_CODE ALERT_COLOUR
---------- ------------
(null)     (null)       
50001      RED          
50005      (null)

Which doesn't match the result you said you expected, but as I said in a comment, I'm not sure how you got from the numbers to the result you showed. Your buckets exclude a lot of possibilities; you might want the first condition to just be > 5 for example. You're completely ignoring 50005, which you expected to see. If your underlying logic is that any code set uses the 2-day count and nulls use the 1-day count - which is a leap - then you could generalise it a bit to:

select error_code,
  case
    when error_code is not null and cnt_not_null between 5 and 100 then 'RED'
    when error_code is not null and cnt_not_null < 5 then 'GREEN'
    when error_code is null and cnt_null > 100 then 'RED'
    when error_code is null and cnt_null between 5 and 100 then 'GREEN'
    else null
  end as alert_colour
from (
  select error_code,
    count(case when cret_dtim > trunc(sysdate - 2)
      and error_code is not null then 1 end) as cnt_not_null,
    count(case when cret_dtim > trunc(sysdate - 1)
      and error_code is null then 1 end) as cnt_null
  from error_log
  where cret_dtim > trunc(sysdate - 2)
  group by error_code
)
order by error_code nulls first;

ERROR_CODE ALERT_COLOUR
---------- ------------
(null)     (null)       
50001      RED          
50005      GREEN

Which still isn't your expected result, but will have to do until the logic (and my mistake) is explained, or your results are changed...

thorough and nice explanation. – radar Jan 06 '15 at 18:31 — radar, Jan 06 '15 at 18:31

score 0 · Answer 2 · answered Jan 06 '15 at 15:24

case Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE. Try this.

SELECT ERROR_CODE,
       CASE
         WHEN (SELECT Count(ERROR_CODE)
               FROM   ERROR_LOG
               WHERE  CRET_DTIM > Trunc(SYSDATE - 2)
                      AND ERROR_CODE = '50001') between 50 and 100 THEN 'RED'
         WHEN (SELECT Count(ERROR_CODE)
               FROM   ERROR_LOG
               WHERE  CRET_DTIM > Trunc(SYSDATE - 2)
                      AND ERROR_CODE = '50001') < 50 THEN 'GREEN'
         WHEN (SELECT Count(ERROR_CODE)
               FROM   ERROR_LOG
               WHERE  CRET_DTIM > Trunc(SYSDATE - 1)
                      AND ERROR_CODE IS NULL) > 100 THEN 'RED'
         WHEN (SELECT Count(ERROR_CODE)
               FROM   ERROR_LOG
               WHERE  CRET_DTIM > Trunc(SYSDATE - 1)
                      AND ERROR_CODE IS NULL) between 50 and 100 THEN 'GREEN'
       END AS ALERT_COLOR
FROM   ERROR_LOG
GROUP  BY ERROR_CODE

@NullPointerException - can you show some sample records and desired result. — Pரதீப், Jan 06 '15 at 15:40

Count condition in CASE clause

2 Answers2