时间戳

首页 > 解决方案 > 如何将 pl/sql 查询更改为 2“WHERE”子句

oracle - 如何将 pl/sql 查询更改为 2“WHERE”子句

问题描述

我有 PL/SQL 查询对于一个交互式网格有 2 个不同的 WHERE 子句 ( WHERE(1), WHERE(2))，我有一个参数 if parameter has value is 1then PL/SQL query will run with WHERE(1)else parameter has value is 2then PL/SQL query run with WHERE(2), 希望有人可以解决我的问题，非常感谢。这是我的 PL/SQL 查询：

if :P140101101_CHANGE = 1 then
select T.RDE_ID,
          T.RDE_SIP,
          T.V_IIT_CODE,
          T.V_IIT_NAME
from V_REQUISITION_DETAILS_V6 T
WHERE (T.REQ_ID = :P140101101_V_REQ_ID)

if :P140101101_CHANGE = 2 then
SELECT T.RDE_ID,
       T.RDE_SIP,
       T.V_IIT_CODE,
       T.V_IIT_NAME
  FROM V_REQUISITION_DETAILS_V6 T
 WHERE (T.REQ_ID = ::P140101101_V_REQ_ID) 
   AND EXISTS (SELECT 1
          FROM V_PRICE_LIST_LINES_PO32 PRI
         WHERE PRI.Iit_Id = T.Iit_Id)
   and (T.PLL_ID IS NULL)

标签： oracleplsqloracle-apex

解决方案

您可以OR按如下方式使用条件：

SELECT T.RDE_ID,
       T.RDE_SIP,
       T.V_IIT_CODE,
       T.V_IIT_NAME
  FROM V_REQUISITION_DETAILS_V6 T
 WHERE (T.REQ_ID = :P140101101_V_REQ_ID) 
   AND ( :P140101101_V_REQ_ID <> 2 OR (EXISTS (SELECT 1
          FROM V_PRICE_LIST_LINES_PO32 PRI
         WHERE PRI.Iit_Id = T.Iit_Id)
   and (T.PLL_ID IS NULL)))

更新：4个条件

SELECT T.RDE_ID,
       T.RDE_SIP,
       T.V_IIT_CODE,
       T.V_IIT_NAME
  FROM V_REQUISITION_DETAILS_V6 T
 WHERE (T.REQ_ID = :P140101101_V_REQ_ID) 
-- condition for 1 is irrelevant here  
-- AND ( :P140101101_SREACH_PRICE <> 1 OR (T.REQ_ID = :P140101101_V_REQ_ID))
   AND ( :P140101101_SREACH_PRICE <> 2 OR (EXISTS (SELECT 1
                                                    FROM V_PRICE_LIST_LINES_PO32 PRI
                                                     WHERE PRI.Iit_Id = T.Iit_Id)
                                                     and (T.PLL_ID IS NULL)))
   AND ( :P140101101_SREACH_PRICE <> 3 OR (NOT EXISTS (SELECT 1
                                                       FROM V_PRICE_LIST_LINES_PO32 PRI
                                                       WHERE PRI.Iit_Id = T.Iit_Id))) 
   AND ( :P140101101_SREACH_PRICE <> 4 OR (T.PLL_ID IS NOT NULL))

推荐阅读