Tuesday, December 6, 2011

Decode VS Case in Oracle

Decode: In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.

select decode(col1, 1,one,2,two) from table_name


if col1 value 1 then it will print as 'one' in result
if col1 value 2 then it will print as 'two' in result

decode will give performance issue. so now CASE came with same funtionality with better performance.

Case:

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;

No comments: