CASE in where clause.

rupen · August 25th, 2009, 02:53 AM

Hi,
wanted to include where clause conditionally (depends on variable value passed to procedure)

Code:

select main_acnt_code as code, main_acnt_name as name
from fm_main_account
where MAIN_FRZ_FLAG = 'N'
and case in_Code
        when 'CAPEX' THEN main_class_code_05 = 'Y';
        when 'MAIN_ACC2' THEN main_class_code_02 = 'Y';
     end case;

but error reads as "missing keywords " at line "when'CAPEX'THENmain_class_code_05 = 'Y';"

any idea how can we make select statement's where clause dynamic ??

om_prakash · August 25th, 2009, 04:53 AM

You can re-write the SQL as follows:
select main_acnt_code as code, main_acnt_name asname
from fm_main_account
where MAIN_FRZ_FLAG ='N'
and
((in_Code ='CAPEX'AND main_class_code_05 ='Y')
OR(in_Code ='MAIN_ACC2'AND main_class_code_02 ='Y'))

rupen · August 27th, 2009, 04:39 AM

What an idea sirji....Thank you...