[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4426
  • Last Modified:

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?



0
peadar86
Asked:
peadar86
  • 4
  • 2
  • 2
1 Solution
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
peadar86Author Commented:

 Thanks pratiloy

 That works fine.

 peadar86
0
 
pratikroyCommented:
Glad I could help. Cheers !
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now