SQl Plus: If..elsif or case statement

My student table has a field called key_stage which needs to be set to either 1,2,3 or 4 depending on date of birth (dob):

If dob between 02-jul-1996 and 01-jul-2000 then 1
If dob between 02-jul-1993 and 01-jul-1996 then 2
If dob between 02-jul-1990 and 01-jul-1993 then 3
If dob between 02-jul-1988 and 01-jul-1990 then 4

How can I run this with an if..elsif or case statement using
sql plus?



peadar86Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pratikroyCommented:
select case when dob between to_date('02-jul-96') and to_date('01-jul-00') then 1
                 when dob between to_date('02-jul-93') and to_date('01-jul-96') then 2
                 when dob between to_date('02-jul-90') and to_date('01-jul-93') then 3
                 when dob between to_date('02-jul-88') and to_date('01-jul-90') then 4 end as key_stage
from student;
0
slightwv (䄆 Netminder) Commented:
Pre 9i (it gets real ugly):



select dob, DECODE('IN RANGE',
  DECODE(GREATEST(trunc(dob),   to_date('02-jul-1988','DD-MON-YYYY')), LEAST(trunc(dob), to_date('01-jul-1990','DD-MON-YYYY')),'IN RANGE','NOT IN RANGE'),'4',
  DECODE('IN RANGE',
  DECODE(GREATEST(trunc(dob), to_date('02-jul-1990','DD-MON-YYYY')), LEAST(trunc(dob), to_date('01-jul-1993','DD-MON-YYYY')),'IN RANGE','NOT IN RANGE'),'3',
  DECODE('IN RANGE',
  DECODE(GREATEST(trunc(dob),to_date('02-jul-1993','DD-MON-YYYY')), LEAST(trunc(dob),to_date('01-jul-1996','DD-MON-YYYY')),'IN RANGE','NOT IN RANGE'),'2',
  DECODE('IN RANGE',
  DECODE(GREATEST(trunc(dob),to_date('02-jul-1996','DD-MON-YYYY')), LEAST(trunc(dob),to_date('01-jul-2000','DD-MON-YYYY')),'IN RANGE','NOT IN RANGE'),'1',
                                                                     '???'))))  id_range
from tab1
/
0
peadar86Author Commented:

 
 I need to update the student table with the key_stage value rather than just selecting.
 How would I adapt these statements to do this?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
drop table tab1;
create table tab1(col1 char(1), col2 char(1));

insert into tab1(col1) values('a');
insert into tab1(col1) values('b');
insert into tab1(col1) values('c');
commit;


update tab1 t1 set col2=(
select
            case col1
            when 'a' then '1'
            when 'b' then '2'
            end
from tab1 t2 where t1.col1=t2.col1
)
;
0
pratikroyCommented:
Both columns (DOB and KEY_STAGE) are in the same table ?

If so then try this :

update table1
set KEY_STAGE = (case when DOB between to_date('02-jul-96') and to_date('01-jul-00') then 1
                                   when DOB between to_date('02-jul-93') and to_date('01-jul-96') then 2
                                   when DOB between to_date('02-jul-90') and to_date('01-jul-93') then 3
                                   when DOB between to_date('02-jul-88') and to_date('01-jul-90') then 4 end);
0
pratikroyCommented:
in your case table1 is student, so :

update STUDENT
set KEY_STAGE = (case when DOB between to_date('02-jul-96') and to_date('01-jul-00') then 1
                                   when DOB between to_date('02-jul-93') and to_date('01-jul-96') then 2
                                   when DOB between to_date('02-jul-90') and to_date('01-jul-93') then 3
                                   when DOB between to_date('02-jul-88') and to_date('01-jul-90') then 4 end);
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
peadar86Author Commented:

 Thanks pratiloy

 That works fine.

 peadar86
0
pratikroyCommented:
Glad I could help. Cheers !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.