IN operator within CASE statement in where clause

Question

When p_type is null, then I have to provide two values i.e ('value1','value2'). How can I achieve this using Case or Decode statement in where clause? Below PL/SQL block is not valid and throws an error of missing keyword: statement ignored.

Declare
l_xml CLOB;
p_type varchar2(200) := 'value1'; -- can be null
begin
SELECT XMLAGG (
            XMLELEMENT ("Address",
            (XMLFOREST (usg.code as "CODE",
            usg.date as "Date"
            )))) .getclobval()
             into l_xml
                        FROM  usg

WHERE usg.number = '12345'  
AND usg.code = p_type
OR CASE WHEN p_type IS NULL THEN usg.code in ('value1','value2') END;

dbms_output.put_line (l_xml);

end;

Possible duplicate of [Conditional WHERE clause in SQL Server](https://stackoverflow.com/questions/18629132/conditional-where-clause-in-sql-server) — Gauravsa, Oct 02 '18 at 03:40

score 0 · Answer 1 · answered Oct 02 '18 at 03:07

0

You don't need a CASE in this particular instance.

WHERE usg.number = '12345'  
AND (usg.code = p_type
     OR
     (p_type IS NULL AND usg.code in ('value1','value2'))
    )

answered Oct 02 '18 at 03:07

Jeffrey Kemp

59,135
14
106
158

score -2 · Answer 2 · answered Oct 02 '18 at 02:55

-2

Case statement should be part of the select statement and not where conditions

answered Oct 02 '18 at 02:55

Vijay Balebail

185
5

1

(1) you can't use a case *statement* anywhere in a sql query; (2) you can use a case *expression* anywhere in a sql query where an expression is allowed, including the select and where clauses. – Jeffrey Kemp Oct 02 '18 at 03:08

IN operator within CASE statement in where clause

2 Answers2