x

CASE statement

I am trying to write an anonymous procedure that based on certain criteria updates a column in my table. I am trying to assign a grade to employees based on salary ranges. So below is a basic example of what i am doing:

DECLARE
v_empid      employees.ssn%type
v_sal        employees.salary%type;
v_labgrade   employees.labor_grade%type;   
BEGIN
CASE 
WHEN v_sal between 700  and 1200 THEN v_labgrade := 1 ;
WHEN v_sal between 1201 and 1400 THEN v_labgrade := 2 ;
WHEN v_sal between 1401 and 2000 THEN v_labgrade := 3 ;
WHEN v_sal between 2001 and 3000 THEN v_labgrade := 4 ;
WHEN v_sal between 3001 and 9999 THEN v_labgrade := 5 ;
ELSE v_sal := 0;
END CASE;
UPDATE employees SET labor_grade = v_labgrade where ssn = v_empid  ;
END;
/
The procedure I wrote compiles sucessfully but does not insert anything into my labor_grade column..Any idea what I am missing? I am using oracle.
more ▼

asked Sep 29, 2011 at 04:59 PM in Default

sqlnewb gravatar image

sqlnewb
216 27 30 31

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

it looks like v_empid was never assigned a value, so it's not able to match any rows in the table.

I put together a table with some sample data and put together a quick working version of the query. Below I present a couple variations on the theme including one that uses a stored procedure so you can pass values in remotely. If your intent is to use a looping construct within the anonymous block, you can find some basic guidance on anonymous procedures here - [http://www.java2s.com/Tutorial/Oracle/0480__PL-SQL-Programming/Thisisananonymousproceduresoithasnoname.htm][1]

/* lets start off with an employees table and 10 employees... */
create table employees (emp_id int, ssn char(3), salary int, labor_grade int);

insert into employees(emp_id, ssn, salary, labor_grade) values (1, 'abc', 750, 0);
insert into employees(emp_id, ssn, salary, labor_grade) values (2, 'def', 1150, 0);
insert into employees(emp_id, ssn, salary, labor_grade) values (3, 'ghi', 1250, 0);
insert into employees(emp_id, ssn, salary, labor_grade) values (4, 'jkl', 1350, 0);
insert into employees(emp_id, ssn, salary, labor_grade) values (5, 'mno', 1450, 0);
insert into employees(emp_id, ssn, salary, labor_grade) values (6, 'pqr', 1550, 0);
insert into employees(emp_id, ssn, salary, labor_grade) values (7, 'stu', 2650, 0);
insert into employees(emp_id, ssn, salary, labor_grade) values (8, 'vwx', 3760, 0);
insert into employees(emp_id, ssn, salary, labor_grade) values (9, 'yz&', 4850, 0);

/* here are our 10 employees before we make any changes...*/
select * from employees order by emp_id;

/* first we'll use an anynomous block as per the original question
   if we aren't going to use the variables anywhere else in the batch
   we may not need them at all.  More on that later... */
DECLARE
v_empid employees.ssn%type := 'stu' ;
v_sal employees.salary%type := 2001;
v_labgrade employees.labor_grade%type;
BEGIN

case
    WHEN v_sal between 700  and 1200 THEN v_labgrade := 1;
    WHEN v_sal between 1201 and 1400 THEN v_labgrade := 2;
    WHEN v_sal between 1401 and 2000 THEN v_labgrade := 3;
    WHEN v_sal between 2001 and 3000 THEN v_labgrade := 4;
    WHEN v_sal between 3001 and 9999 THEN v_labgrade := 5;
    ELSE v_labgrade :=0; /* original question set v_sal := 0 here.
                            I treated that as a typo and used v_labgrade
                            like the others.  Revert if appropriate */
end case;

update employees
set labor_grade = v_labgrade
where ssn = v_empid;
commit;
end;
/

/* here are our employees with 'stu' updated... */
select * from employees order by emp_id;

/* Mentioned earlier that we may not need all the variables unless they're used
   in some code that wasn't included in the original question.  If they aren't,
   here is the anonymous block without two of the variables.  For this one, I
   took the liberty of using the salary column in the employees table that was
   referenced in the original variable declaration and let the case statement
   operate on the labor_grade column directly... */
DECLARE
v_empid employees.ssn%type := 'yz&' ;
BEGIN

/* just put a case statement directly in the update... */
update employees
set labor_grade = case
    WHEN salary between 700  and 1200 THEN 1
    WHEN salary between 1201 and 1400 THEN 2
    WHEN salary between 1401 and 2000 THEN 3
    WHEN salary between 2001 and 3000 THEN 4
    WHEN salary between 3001 and 9999 THEN 5
    ELSE 0
end
where ssn = v_empid;
commit;
end;
/

/* here are our employees with 'stu' and 'yz&' updated... */
select * from employees;

/* in addition to an anonymous block, we can also use a procedure.
   if this isn't relevant, just ignore it.  I thought I'd include 
   if for the sake of completeness... */
CREATE OR REPLACE PROCEDURE changeGrade
(
v_empid IN employees.ssn%type
/*v_sal IN employees.salary%type,
v_labgrade IN employees.labor_grade%type  */
) IS
BEGIN

update employees
set labor_grade = case
    WHEN salary between 700  and 1200 THEN 1
    WHEN salary between 1201 and 1400 THEN 2
    WHEN salary between 1401 and 2000 THEN 3
    WHEN salary between 2001 and 3000 THEN 4
    WHEN salary between 3001 and 9999 THEN 5
    ELSE 0
END
where ssn = v_empid;
commit;
end changeGrade;
/

exec changeGrade('mno');

/* here are our employees with 'stu', 'yz&' and 'mno' updated... */
select * from employees order by emp_id;   

drop procedure changeGrade;   
drop table employees;

Disclaimer - I'm not an oracle developer, I just play one on the SQL Server forum, so let me know if I completely missed the mark.

[1]: http://www.java2s.com/Tutorial/Oracle/0480__PL-SQL-Programming/Thisisananonymousproceduresoithasnoname.htm
more ▼

answered Sep 30, 2011 at 10:09 PM

KenJ gravatar image

KenJ
19.2k 1 3 11

+1 for the disclaimer alone :)
Sep 30, 2011 at 11:58 PM Blackhawk-17
Thanks That was helpful!
Oct 01, 2011 at 12:48 PM sqlnewb
(comments are locked)
10|1200 characters needed characters left

What happens I v_sal is NULL? The case will pass without assigning any value to v_label grade

According to your code, NULL are not handled by the CASE. NULL is not the same as 0. That means that

DECLARE @x AS INT =NULL
SELECT 
    CASE @x 
       WHEN 0 THEN 'NULL equals 0' 
       WHEN NULL THEN 'NULL is not the same as 0'
    END 
This will give you 'NULL is not the same as 0' (at least SQL server works that way). Try to add the NULL evaluation in you case and see what happens.
more ▼

answered Sep 29, 2011 at 11:26 PM

Håkan Winther gravatar image

Håkan Winther
15.6k 34 37 48

If it doesn't fall in the range it will be assigned a 0
Sep 30, 2011 at 06:12 AM sqlnewb
(comments are locked)
10|1200 characters needed characters left

Not quite sure about the oracle syntax, But I can see a problem with your update statement, there is no where clause!!! Don't you need another variable like, v_emplID then you can add the v_emplid to the update statement

So everytime it is run, the last v_sal, every row in the employees table will have the same labor_grade.
more ▼

answered Sep 29, 2011 at 08:33 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 10 12 14

This site have merged with oracleoverflow
Sep 29, 2011 at 11:28 PM Håkan Winther
aah thanks Hakan, forgot about that
Sep 29, 2011 at 11:33 PM Daniel Ross
I added a where clause see above code: still not inserting? is my where clause wrong?
Sep 30, 2011 at 06:14 AM sqlnewb
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x378
x39

asked: Sep 29, 2011 at 04:59 PM

Seen: 927 times

Last Updated: Sep 30, 2011 at 06:16 AM