如何将pl/sql查询转换为2个WHERE子句

Question

我有一个交互式网格的PL/SQL查询有两个不同的WHERE子句(WHERE(1)，WHERE(2))，并且我有一个参数，如果参数的值为1，则PL/SQL查询将以WHERE(1)运行，否则参数为2，则PL/SQL查询将以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)

Answer 1

要做到这一点，最简单的方法是将其划分为2个交互式网格区域。在每个网格的服务器端条件部分，选择Item = Value类型并相应地应用条件:P140101101_CHANGE = 1或2

另一种方法是使用Union (我还没有测试过这个方法，但它应该可以工作)

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_CHANGE = 1

UNION

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)
   AND :P140101101_CHANGE = 2

Answer 2

您可以按如下方式使用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)) 

Answer 3

这是4个条件：@Tejash

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_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))