case语句和in case语句

Question

我不确定我需要什么样的声明才能完成我需要做的事情。我想在我目前拥有的case语句之后添加一个case语句。Bellow是case语句。

CASE
    WHEN LOCOFF.EXT_SRV_POLYGON IN ('BOF', 'CDA', 'COL', 'DAC', 'GOS', 'KEL', 'KLF', 'LAG', 'LEC', 'MED', 'PUM', 'RIT', 'ROS', 'SAN', 'SPO')
    THEN 'GAS'
    WHEN LOCOFF.EXT_SRV_POLYGON IN ('CDC', 'COC', 'DAV', 'DPC', 'GRC', 'KEC', 'LCC', 'OTC', 'PAC', 'SAC', 'SPC')
    THEN 'ELECTRIC'
    ELSE 'MISSING'
  END AS TYPE, 

我想说，如果EXT_Distworktype是GC或GT，并且类型是电动的，请给我一个表示错误多边形的类型，或者如果EXT_DISTWORKTYPE是EC，ES，或ET，并且类型是气体，那么给我一个说错误多边形的类型。

以下是我的完整查询：

SELECT WO.WONUM AS "Work Order",
  WO.LOCATION   AS "Location",
  WO.STATUS     AS "Status",
  WO.DESCRIPTION,
  WO.ACTFINISH AS "Actual Finish",
  WO.PARENT    AS "Parent WO",
  WO.WORKTYPE  AS "Work Type",
  CASE
    WHEN WO.REPORTDATE IS NULL
    THEN TO_DATE('29-JAN-15 00:00:00', 'DD-MON-YY HH24:MI:SS')
    ELSE WO.REPORTDATE
  END       AS "Reported Date",
  WO.SITEID AS "Site",
  CASE
    WHEN WO.ACTFINISH IS NULL
    THEN GREATEST(TRUNC(TO_DATE(SysDate, 'DD-MON-YY HH24:MI:SS')      - TO_DATE(WO.REPORTDATE, 'DD-MON-YY HH24:MI:SS'), 6), 0)
    WHEN WO.STATUS NOT                                               IN ('COMP')
    THEN GREATEST(TRUNC(TO_DATE(SysDate, 'DD-MON-YY HH24:MI:SS')      - TO_DATE(WO.REPORTDATE, 'DD-MON-YY HH24:MI:SS'), 6), 0)
    WHEN WO.STATUS                                                   IN ('COMP')
    THEN GREATEST(TRUNC(TO_DATE(WO.ACTFINISH, 'DD-MON-YY HH24:MI:SS') - TO_DATE(WO.REPORTDATE, 'DD-MON-YY HH24:MI:SS'), 6), 0)
  END AS "Age",
  WO.EXT_DISTWORKTYPE,
  LOCOFF.EXT_OFFICE,
  CASE
    WHEN LOCOFF.EXT_SRV_POLYGON IN ('BOF', 'CDA', 'COL', 'DAC', 'GOS', 'KEL', 'KLF', 'LAG', 'LEC', 'MED', 'PUM', 'RIT', 'ROS', 'SAN', 'SPO')
    THEN 'GAS'
    WHEN LOCOFF.EXT_SRV_POLYGON IN ('CDC', 'COC', 'DAV', 'DPC', 'GRC', 'KEC', 'LCC', 'OTC', 'PAC', 'SAC', 'SPC')
    THEN 'ELECTRIC'
    ELSE 'MISSING'
  END AS TYPE,
  LOCOFF.EXT_STATECODE,
  WO.OWNERGROUP,
  WO.EXT_JOBCODE
FROM LOCATIONS LOCOFF
RIGHT JOIN WORKORDER WO
ON WO.LOCATION       = LOCOFF.LOCATION
WHERE WO.STATUS NOT IN ('CLOSE', 'WAIV', 'CAN', 'REJ', 'REVOKED')
AND LOCOFF.SITEID    = 'OPS'
AND WO.EXT_JOBCODE NOT LIKE 'A%' AND WO.E`enter code here`XT_JOBCODE NOT LIKE 'B%'
AND WO.EXT_JOBCODE NOT IN ('K008','K009','I006','I007','I008');

Answer 1

如果您只想修改现有的列值(而不是添加包含“wrong”消息的单独列)，则可以嵌套case表达式：

CASE
    WHEN LOCOFF.EXT_SRV_POLYGON IN ('BOF', 'CDA', 'COL', 'DAC', 'GOS', 'KEL', 'KLF', 'LAG', 'LEC', 'MED', 'PUM', 'RIT', 'ROS', 'SAN', 'SPO')
    THEN
        CASE
            WHEN WO.EXT_DISTWORKTYPE IN ('EC', 'ES', 'ET')
            THEN 'WRONG POLYGON'
            ELSE 'GAS'
        END
    WHEN LOCOFF.EXT_SRV_POLYGON IN ('CDC', 'COC', 'DAV', 'DPC', 'GRC', 'KEC', 'LCC', 'OTC', 'PAC', 'SAC', 'SPC')
    THEN
        CASE
            WHEN WO.EXT_DISTWORKTYPE IN ('GC', 'GT')
            THEN 'WRONG POLYGON'
            ELSE 'ELECTRIC'
        END
    ELSE 'MISSING'
  END AS TYPE, 

使用相同的虚构数据；your original case和this nested case快速演示SQL Fiddle，它提供：

EXT EX TYPE        
--- -- -------------
BOF GC GAS          
CDA GT GAS          
SPO ES WRONG POLYGON
CDC EC ELECTRIC     
COC ET ELECTRIC     
SPC GC WRONG POLYGON

这比尝试执行第二步来解释TYPE值要简单一些，因为您不能在同一查询级别中引用列别名；您需要将现有查询转换为内联视图。

Answer 2

一个额外的案例不能解决这个问题吗？

CASE
  WHEN WO.EXT_DISTWORKTYPE IN ('GC', 'GT') 
  AND LOCOFF.EXT_SRV_POLYGON IN ('CDC', 'COC', 'DAV', 'DPC', 'GRC', 'KEC', 'LCC', 'OTC', 'PAC', 'SAC', 'SPC') --electronic
  THEN 'WRONG POLYGON'
CASE 
  WHEN WO.EXT_DISTWORKTYPE IN ('EC', 'ES', 'ET' ) 
  AND LOCOFF.EXT_SRV_POLYGON IN ('BOF', 'CDA', 'COL', 'DAC', 'GOS', 'KEL', 'KLF', 'LAG', 'LEC', 'MED', 'PUM', 'RIT', 'ROS', 'SAN', 'SPO') --gas
  THEN 'WRONG POLYGON'
ELSE 'MSSING'
END AS YOURANSWER,

您可以在case中执行case

CASE 
  WHEN (TRUE) THEN
    CASE WHEN (true) THEN 'a' ELSE 'b' END
  ELSE 
     'A' 
END