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)