Solved

SQl Plus: If..elsif or case statement

Posted on 2004-10-26
4,409 Views
Last Modified: 2008-01-09
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
Question by:peadar86
    8 Comments
     
    LVL 9

    Expert Comment

    by: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;
    0
     
    LVL 75

    Expert Comment

    by: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
    /
    0
     

    Author Comment

    by:peadar86

     
     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
     
    LVL 75

    Expert Comment

    by: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
    )
    ;
    0
     
    LVL 9

    Expert Comment

    by: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);
    0
     
    LVL 9

    Accepted Solution

    by:
    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
     

    Author Comment

    by:peadar86

     Thanks pratiloy

     That works fine.

     peadar86
    0
     
    LVL 9

    Expert Comment

    by:pratikroy
    Glad I could help. Cheers !
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now