|
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: 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.
(comments are locked)
|
|
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 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 for the disclaimer alone :)
Sep 30 '11 at 11:58 PM
Blackhawk-17
Thanks That was helpful!
Oct 01 '11 at 12:48 PM
sqlnewb
(comments are locked)
|
|
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 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. If it doesn't fall in the range it will be assigned a 0
Sep 30 '11 at 06:12 AM
sqlnewb
(comments are locked)
|
|
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. This site have merged with oracleoverflow
Sep 29 '11 at 11:28 PM
Håkan Winther
aah thanks Hakan, forgot about that
Sep 29 '11 at 11:33 PM
Daniel Ross
I added a where clause see above code: still not inserting? is my where clause wrong?
Sep 30 '11 at 06:14 AM
sqlnewb
(comments are locked)
|

