asked on

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?

pratikroy

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;

slightwv (䄆 Netminder)

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
/

peadar86

ASKER

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?

slightwv (䄆 Netminder)

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
)
;

pratikroy

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);

ASKER CERTIFIED SOLUTION

pratikroy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

peadar86

ASKER

Thanks pratiloy

That works fine.

peadar86

pratikroy

Glad I could help. Cheers !