在 Oracle 的 Case 塊中使用聯合條件

我們如何在 Oracle 的 Case 塊中使用聯合條件。嘗試以下程式碼但不工作。

SELECT NVL(SUM(DECR(BUDGET, 'AcKnOw%eDg3h1dD3N$4mH3RE')),0) AS budget
INTO v_budget
FROM MST_ESTIMATES
WHERE (PMT, MOTS_ACR, ENTRY_TYPE, type) IN ( (
 CASE
   WHEN (ind_est_type ='TBD'
   AND oomLockFlag    = 1)
   THEN
     (SELECT trim(getPrjData.PMT),
       trim(getPrjData.mots_acr),
       'PROJ',
       'OOM'
     FROM DUAL
     )
   UNION END )
SELECT PMT,
 MOTS_ACR,
 entry_type,
 ind_est_type AS type
FROM MST_EST_LOCKS
WHERE PMT    = trim(getPrjData.PMT)
AND MOTS_ACR = trim(getPrjData.mots_acr) (
 CASE
   WHEN ind_est_type='OOM'
   THEN
   AND OOM          = 'Y'
   WHEN ind_est_type= 'TCP1'
   THEN
   AND TCP1          = 'Y'
   WHEN ind_est_type = 'FB'
   THEN
   AND FB = 'Y'
 END ) )
AND userid IN
 (SELECT userid
 FROM mst_employee
 WHERE director = ev_director
 AND manager    = ev_manager
 )
AND firm = ev_firm; 

有人可以幫忙嗎？

用標準條件替換 CASE 塊

... AND  (ind_est_type='OOM' and OOM = 'Y' OR
         ind_est_type= 'TCP1' and TCP1 = 'Y' OR
         ind_est_type = 'FB' and FB = 'Y') AND ...

不要忘記括號，因為 AND 比 OR 具有更高的優先級，以便將這部分與周圍的 AND 分離。

–

UNION關鍵字在一個奇怪的地方。代替

...
UNION END )
SELECT PMT,
...

它應該是

...
END )
UNION
SELECT PMT,
...

因為END )是用大括號括起來的 CASE 語句的結尾。您想要前一個 SELECT 與下一個的聯合。

如果你堅持使用CASE，你可以做到

... AND CASE 
     WHEN ind_est_type='OOM' THEN OOM
     WHEN ind_est_type= 'TCP1' THEN TCP1
     WHEN ind_est_type = 'FB' THEN FB
   END = 'Y' 

  -- or 
  ... AND CASE ind_est_type 
     WHEN 'OOM' THEN OOM
     WHEN 'TCP1' THEN TCP1
     WHEN 'FB' THEN FB
   END = 'Y' 

引用自：https://dba.stackexchange.com/questions/148337