Alex Rivera | Logout

UPDATE with CASE and IN - Oracle

Asked 2011-03-02T17:06:20.587
33

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.

sql oracle-database sql-update
Edit
Report

2 Answers

41

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
answered 2011-03-02T17:28:29.807
3

Use to_number to convert budgpost to a number:

when to_number(budgpost,99999) in (1001,1012,50055) THEN 'BP_GR_A'

EDIT: Make sure there are enough 9's in to_number to match to largest budget post.

If there are non-numeric budget posts, you could filter them out with a where clause at then end of the query:

where regexp_like(budgpost, '^-?[[:digit:],.]+$')
answered 2011-03-02T17:25:30.727

Your Answer