CODEWITHSUNDEEP
oracle-databaseoracle10g
Mohammad Zargarani
Mohammad Zargarani

Reputation: 1422

Use CASE WHEN statement with Sql

I used this query 

SELECT nvl(round(sum(PAP.QTY_PLAN_ANPLN) / 1000), 0) PRODUCTION  
FROM PPC_ANNUAL_PLANS PAP
WHERE PAP.MAAST_ASSET_ID IN( '35390','35392')

Replace where clause with this code

PAP.MAAST_ASSET_ID in (CASE   
        WHEN 'DRI' = 'RMP'
            THEN  '2242'

        WHEN 'DRI' = 'DRI'
            THEN  '35390,35392'

        WHEN 'DRI' = 'SMP'
            THEN  '2241'
   END)

Oracle throw this error

ORA-01722: invalid number

What is the solution to this problem?

Upvotes: 0

Views: 65

Answers (1)

krokodilko
krokodilko

Reputation: 36107

Use OR in the where condition, like this:

where 
 dri = 'DRI' AND MAAST_ASSET_ID in (200,300) 
OR
 dri = 'SMP' AND MAAST_ASSET_ID in (222,333)
OR
 dri = 'RMP' AND MAAST_ASSET_ID in (555,777,888)


if you insist on using CASE expressions, then it certainly can be done in this way:

where 
 1 = case
    when dri = 'DRI' AND MAAST_ASSET_ID in (200,300) THEN 1
    when dri = 'SMP' AND MAAST_ASSET_ID in (222,333) THEN 1
    when dri = 'RMP' AND MAAST_ASSET_ID in (555,777,888) THEN 1
END

but the second condition may kill your database performance for large tables ==> Oracle is able to optimize the first query, but can't do it for the second condition, and it will always use a full table scan on the table in this case.

Upvotes: 1

Related Questions