oracle - Want to convert Dynamic PL/SQL query into static one -

in project there 1 lengthy dynamic query below-

basic_query varchar2(1000); final_query varchar2(1500); where_clause varchar2(500) default null;  basic_query :=<select ...from table ....>  if(<condition1>)          where_clause := <one_new_condition_will_be_added_in_where_clause1> elsif(<condition2>)          where_clause := <one_new_condition_will_be_added_in_where_clause2> elsif(<condition3>)          where_clause := <one_new_condition_will_be_added_in_where_clause3> else       where_clause := <one_new_condition_will_be_added_in_where_clause4> endif;    final_query :=basic_query || where_clause || '<group_by_clause'> || <'order_by_clause'>;  execute immediate final_query; 

now client wants convert dynamic query static one. have tried using case,but it's not working properly.

select ...from table condition1,and  case( when(<condition1> , <one_new_condition_will_be_added_in_where_clause1>)     'valid' when(<condition2> , <one_new_condition_will_be_added_in_where_clause2>     'valid' when(<condition1> , <one_new_condition_will_be_added_in_where_clause3>)     'valid' when(<condition2> , <one_new_condition_will_be_added_in_where_clause4>         'valid'     else 'invalid' end;)='valid 

it checking conditions given in case clause & if 1 of true,it gives output without adding these conditions in clause.

hope understand problem & give solution. in advance.

as said in comment, in principle using case have shown should work (where 'work' mean produce same result; doesn't mean it's better , have performance , maintenance implications etc.). demonstrate i'll create dummy data:

create table t42 (flag varchar2(1), value varchar2(10), cond number); insert t42 values ('y', 'one', 1); insert t42 values ('y', 'two', 2); insert t42 values ('y', 'three', 3); 

... , 2 procedures in package:

create package p42 procedure proc1(parm number); procedure proc2(parm number); end p42; /  create package body p42  procedure proc1(parm number)     basic_query varchar2(1000);     final_query varchar2(1500);     where_clause varchar2(500) default null;     result t42.value%type; begin     basic_query := 'select value t42 flag = ''y'' ';      if parm = 2013         where_clause := 'and cond = 1 ';     elsif parm = 2012         where_clause := 'and cond = 2 ';     else         where_clause := 'and cond = 3 ';     end if;      final_query := basic_query || where_clause;     execute immediate final_query result;     dbms_output.put_line(result); end proc1;  procedure proc2(parm number)     result t42.value%type; begin     select value result t42 flag = 'y'     , case         when parm = 2013 , cond = 1 'valid'         when parm = 2012 , cond = 2 'valid'         when not (parm = 2013 or parm = 2012) , cond = 3 'valid'         else 'invalid' end = 'valid';     dbms_output.put_line(result); end proc2;  end p42; / 

so proc1 doing original dynamic query does, believe. proc2 using case construct. conditions made , variable where clause stupid in form, case.

running various values parm (2013, 2012, 2011) gives same row through both procedures, they're equivalent in sense

exec p42.proc1(2013);  1  exec p42.proc2(2013);  1 

i suspect you've got final condition wrong. of course i'm guessing, since pseudocode you've posted far removed form actual query can't see might have done wrong. if did this:

    , case         when parm = 2013 , cond = 1 'valid'         when parm = 2012 , cond = 2 'valid'         when cond = 3 'valid'         else 'invalid' end = 'valid'; 

... cond = 3 evaluated rows hadn't matched on earlier case, regardless of value of parm - e.g. if parm = 2013 , cond = 3, original dynamic version not have picked up. 2013 you'd match one , three. you'd need exclude parm values you'd used conditions earlier, , @ cond = 3:

    , case         when parm = 2013 , cond = 1 'valid'         when parm = 2012 , cond = 2 'valid'         when not (parm = 2013 or parm = 2012) , cond = 3 'valid'         else 'invalid' end = 'valid'; 

so in pseudocode, might mean:

select ... table condition1 ,  case     when (<condition1> , <one_new_condition_will_be_added_in_where_clause1>)         'valid'     when (<condition2> , <one_new_condition_will_be_added_in_where_clause2>         'valid'     when (<condition3> , <one_new_condition_will_be_added_in_where_clause3>)         'valid'     when (not (<condition1> or <condition2> or <condition3>)         , <one_new_condition_will_be_added_in_where_clause4>)         'valid'     else 'invalid' end = 'valid' 

i'm not saying idea, ought work. without knowing why client objects dynamic sql (all of it? specific aspect of query? impact you're having on database, e.g. through not using bind variables?) , real query doing it's impossible know if approach appropriate. there may better way, , might improving dynamic version know...