sql - Oracle Procedure: Creating dynamic WHERE clause -

sql - Oracle Procedure: Creating dynamic WHERE clause -

i creating dynamic clause, its' reporting error(40,51): pl/sql: ora-00905: missing keyword @ column_2 in ('x','y','z'), error remains other operators well.

select * test_table column_1 = <some_value> , (case column_2 when 'a' column_2 in ('x','y','z') column_3 in (1, 2) when 'b' column_2 in ('p', 'q', 'r') column_3 in (2, 3) else column_2 = 'x' column_3 = 1 end case);

also need add together status passed in parameters in not null, this

where column_1 = <some_value> if (param_value <> null) column_2 = param_value end if

please suggest how can accomplish desire.

you can't dynamically "attach" in clauses that, there few different ways this:

where column_1 = <some_value> , ( (column_2 = 'a' , column_2 in ('x','y','z') , column_3 in (1, 2)) or (column_2 = 'b' , column_2 in ('p', 'q', 'r') , column_3 in (2, 3)) or (column_2 not in ('a','b') , column_2 = 'x' column_3 = 1) )

or

where column_1 = <some_value> , (case column_2 when 'a' (column_2 = 'x' or column_2 = 'y' or column_2 = 'z') , (column_3 = 1 or column_3 = 2) when 'b' (column_2 = 'p' or column_2 = 'q' or column_2 = 'r') , (column_3 = 2 or column_3 = 3) else column_2 = 'x' , column_3 = 1 end case);

or seaparate query each case status , union them.

sql oracle procedure