无数据的SQL CASE语句
我有一个表,其中包含具有运行状态的流程引擎1、2、3、4、5、6。当其中一个引擎出现故障时,该记录将从表中删除。使用CASE语句,我可以显示第一个出现故障的引擎,但是如果有两个或多个引擎出现故障,我该如何开始显示这些引擎呢?例如,如果两个引擎都停机,我如何使此查询显示PE 2停机和PE 4停机。目前,它仅显示列表中关闭的第一个引擎。
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;
解决方案
而不是Case,对两种不同的情况使用union all
,都很好,有些不运行。子查询分解以减少重复代码。
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)
相关文章