预言机:在 where 子句中使用 case 语句

Question

在Oracle 12(和APEX(中，我在WHERE子句中的CASE语句中遇到问题。 该方案是主表、ORDER 和PRODUCTS_BOUGHT表，因此这是一对多关系。 我有一份报告，PRODUCTS_BOUGHT有一个过滤器。 筛选器填充名为 :P 36_产品列表的绑定变量/APEX 页面项。如果用户选择给定产品，我希望报告仅显示包含给定产品的订单。 过滤器包含"全部"一词，它不应该进行任何过滤，以及我们携带的每个产品。

我的 SQL 语句是

Select distinct   
    :P36_PRODUCT_LISTING,    
     LISTAGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name)   OVER (PARTITION BY B.SALES_ORDER_NUMBER) AS products,
    ...
from ORDER A, PRODUCTS_BOUGHT B
where 
 A.SALES_ORDER_NUMBER = B.SALES_ORDER_NUMBER
 and
case when
 :P36_PRODUCT_LISTING = 'All' then 1 = 1
else a.SALES_ORDER_NUMBER IN (SELECT SALES_ORDER_NUMBER from PRODUCTS_BOUGHT where PRODUCT_NAME = :P36_PRODUCT_LISTING) end 

当我运行该语句时，我得到的错误是缺少关键字。我做错了什么？

Answer 1

不要使用 case . 只需使用布尔逻辑：

where (:P36_PRODUCT_LISTING = 'All' or
       a.SALES_ORDER_NUMBER IN (SELECT SALES_ORDER_NUMBER
                                from PRODUCTS_BOUGHT
                                where PRODUCT_NAME = :P36_PRODUCT_LISTING
                               ) 
      ) 

case的问题(正如您所编写的那样(是 Oracle 不会将逻辑表达式中的值视为有效值。 有些数据库可以，但 Oracle 没有。

另外：

• 不要在from子句中使用逗号。 始终使用正确、明确的join语法。
• 使用作为表名称缩写的表别名。 更容易阅读查询和修复错误。
• 这里不需要select distinct。 您正在执行没有group by的聚合，因此无论如何只有一行。

所以：

 Select :P36_PRODUCT_LISTING,    
        LISTAGG(b.product_name, ', ') WITHIN GROUP (ORDER BY b.product_name)   OVER (PARTITION BY B.SALES_ORDER_NUMBER) AS products,
    ...
from ORDER o join
     PRODUCTS_BOUGHT B
     on p.SALES_ORDER_NUMBER = B.SALES_ORDER_NUMBER
    where (:P36_PRODUCT_LISTING = 'All' or
           o.SALES_ORDER_NUMBER IN (SELECT SALES_ORDER_NUMBER
                                    from PRODUCTS_BOUGHT
                                    where PRODUCT_NAME = :P36_PRODUCT_LISTING
                                   ) 
          ); 

Answer 2

Case 旨在返回值，而不是语句。

尝试 OR 代替

where :P36_PRODUCT_LISTING = 'All' 
or (:P36_PRODUCT_LISTING <> 'All'
    and a.SALES_ORDER_NUMBER IN (SELECT SALES_ORDER_NUMBER from PRODUCTS_BOUGHT where PRODUCT_NAME = :P36_PRODUCT_LISTING))