Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Case Statement Error - ORA-00905: missing keyword

189123Feb 11 2020 — edited Feb 11 2020

I have the below SQL

select * from products

where prod_name = 'CASKET'

and (CASE

                WHEN :status = 'FULL' THEN product_no is not null

                WHEN :status = 'OPEN' THEN product_cat IS NOT NULL

                WHEN :status = 'CLOSED' THEN product_no AND product_cat IS NULL

                ELSE NULL

             END)

             I get the below error

            

             ORA-00905: missing keyword

            

What I am trying to acheive is when parameter status = FULL then bring all records with product_no is empty,

status = OPEN then bring all records with product_category is empty

status = CLOSED then bring all records with product_no and product_category is empty.

How can I do this with case statement?

Comments
Post Details
Added on Feb 11 2020
4 comments
19,230 views