CODEWITHSUNDEEP
sqloracle-databasecase
user1009073
user1009073

Reputation: 3238

Oracle: Using Case Statement in Where Clause

In Oracle 12 (and APEX) I am having problems with a CASE statement in a WHERE clause. The scenario is a master table, ORDER, and a PRODUCTS_BOUGHT table, so this is a one to many relationship. I have a report, with a filter on PRODUCTS_BOUGHT. The filter populates a bind variable/APEX page item called :P36_PRODUCT_LISTING. If the user selects a given product, I want the report to just show those orders which contain the given product. The filter contains the word 'All', which should not do any filtering, as well as each product we carry.

My SQL statement is

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

When I run the statement, the error I get is Missing Keyword. What am I doing wrong?

Upvotes: 1

Views: 3241

Answers (2)

JohnHC
JohnHC

Reputation: 11195

Case is designed to return a value, not a statement..

Try OR instead

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Don't use case. Just use boolean logic:

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

The problem with the case (as you have written it) is that Oracle does not treat the value from a logical expression as a valid value. Some databases do, but not Oracle.

In addition:

  • Don't use commas in the from clause. Always use proper, explicit join syntax.
  • Use table aliases that are abbreviations for the table names. Much easier to read the queries and fix bugs.
  • select distinct should not be necessary here. You are doing an aggregation without a group by, so there is only one row anyway.

So:

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

Upvotes: 3

Related Questions