sql - UPDATE with CASE and IN - Oracle

Question

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.

Answer 1

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