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?