Menu
I wrote a query that works like a charm in SQL Server. Unfortunately it needs to be run on an Oracle db. I have been searching the web inside out for a solution on how to convert it, without any success :/
The query looks like this i SQL:
UPDATE tab1 SET budgpost_gr1= CASE WHEN (budgpost in (1001,1012,50055)) THEN 'BP_GR_A' WHEN (budgpost in (5,10,98,0)) THEN 'BP_GR_B' WHEN (budgpost in (11,876,7976,67465)) ELSE 'Missing' END` My problem is also that the columns budgetpost_gr1 and budgetpost is alphanumeric and Oracle seems to want to see the list as numbers. The list are variables/parameters that is pre-defined as comma separated lists, which is just dumped into the query.
559
Introduction to Oracle CASE expression You can use a CASE expression in any statement or clause that accepts a valid expression. For example, you can use the CASE expression in statements such as SELECT , UPDATE , or DELETE , and in clauses like SELECT , WHERE , HAVING , and ORDDER BY .
The CASE expression allows a statement to return one of several possible results, depending on which of several condition tests evaluates to TRUE. You must include at least one WHEN clause within the CASE expression; subsequent WHEN clauses and the ELSE clause are optional.
First, you specify the name of the table which you want to update. Second, you specify the name of the column whose values are to be updated and the new value. If you update more than two columns, you separate each expression column = value by a comma.
In the insert statement, you can we are using a Case statement to define corresponding value to insert in the employee table. In the Case statement, it checks for the required values and inserts values from THEN expression in the table.
You said that budgetpost is alphanumeric. That means it is looking for comparisons against strings. You should try enclosing your parameters in single quotes (and you are missing the final THEN in the Case expression).
UPDATE tab1 SET budgpost_gr1= CASE WHEN (budgpost in ('1001','1012','50055')) THEN 'BP_GR_A' WHEN (budgpost in ('5','10','98','0')) THEN 'BP_GR_B' WHEN (budgpost in ('11','876','7976','67465')) THEN 'What?' ELSE 'Missing' END If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
