question

sqlnewb avatar image
sqlnewb asked

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.
oraclecase-statement
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Daniel Ross avatar image
Daniel Ross answered
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.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site have merged with oracleoverflow
0 Likes 0 ·
aah thanks Hakan, forgot about that
0 Likes 0 ·
I added a where clause see above code: still not inserting? is my where clause wrong?
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If it doesn't fall in the range it will be assigned a 0
0 Likes 0 ·
KenJ avatar image
KenJ answered
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
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 for the disclaimer alone :)
1 Like 1 ·
Thanks That was helpful!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.