Friday, July 29, 2016

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;

No comments:

Post a Comment

Types of animations

Animation is a diverse and creative field with a variety of styles that artists and filmmakers use to bring their visions to life. Here are ...