Skip to content
Advertisement

SQL CASE Statement for no data

I have a table that has process engines 1,2,3,4,5,6 with a running status. When one of the engines is down the record gets deleted from the table. Using a case statement I can display the first engine that is down but how do I go about displaying the engines if 2 or more engines are down. For e.g. how do I make this query display PE 2 IS DOWN and PE 4 is DOWN if both the engines are down. Right now it displays only the first engine in the list that is down .

    SELECT CASE
WHEN (SELECT COUNT(PE_ID) FROM CWVMINFO WHERE PE_ID = 1) = 0 THEN 'PE 1 IS DOWN'
WHEN (SELECT COUNT(PE_ID) FROM CWVMINFO WHERE PE_ID = 2) = 0 THEN 'PE 2 IS DOWN'
WHEN (SELECT COUNT(PE_ID) FROM CWVMINFO WHERE PE_ID = 3) = 0 THEN 'PE 3 IS DOWN'
WHEN (SELECT COUNT(PE_ID) FROM CWVMINFO WHERE PE_ID = 4) = 0 THEN 'PE 4 IS DOWN'
WHEN (SELECT COUNT(PE_ID) FROM CWVMINFO WHERE PE_ID = 5) = 0 THEN 'PE 5 IS DOWN'
WHEN (SELECT COUNT(PE_ID) FROM CWVMINFO WHERE PE_ID = 6) = 0 THEN 'PE 6 IS DOWN'
ELSE 'ALL PROCESS ENGINES ARE UP AND RUNNING'
END "STATUS"
from dual;

Advertisement

Answer

Instead of case, using union all for the two different cases, all good versus some not running. Sub query factoring to reduce repeated code.

with engines as (select level as engine_number 
    from dual 
    connect by level <= 6)
, down_engines as (select engine_number
    from engines
    where engine_number not in (select pe_id from cwvminfo))
select to_char(engine_number) || ' IS DOWN'
from down_engines 
union all
select 'all engines are running'
from dual
where not exists (select null from down_engines)

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement