How to use Case and DECODE function???

How to use Case and DECODE function???

CASE

--Case is used instead of Decode function

--Case is new in 9i

--Case is simple to read and maintain than in Decode

E.g 1) select job,

           case job

                    when 'ANALYST' then 4000

                    when 'MANAGER' then 5000

                    when 'CLERK' then   3000

                    else

                        2000

            end "Raise"

from emp;

E.g 2) select  sal,

                 case sal

                       when 1600 then 'A'

                      when 2850 then 'B'

                else

                     ‘C’

      end "Grade"

from emp;

E.g 3)

 Drop table codes;

create table codes

(traincode varchar2(15));

insert into codes

values('M-D');

insert into codes

values('P-M');

insert into codes

values('P-D');

insert into codes

values('M-A');

insert into codes

values('C-B');

--Here the code values are ellaborated with full forms using case

select traincode,

          CASE traincode   

           when 'M-D' then 'Mumbai to Delhi'

           when 'P-M' then 'Pune to Mumbai'

           when 'P-D' then 'Pune to Delhi'

           when 'M-A' then 'Mumbai to Ahemdabad'

           else

                   'South trains'

            end    

      As "Description"

from codes;

Case in Comparison –

select sal,

case when sal >= 3000 then sal + 10000

     when sal <= 1000 then sal - 500

else

sal

end

from emp;

 

 

DECODE

select job,

            DECODE ( JOB, 'ANALYST','A','MANAGER','M','CLERK','C','ZZ') “CODE”

from emp;