CODEWITHSUNDEEP
sqloracle-databasecase
Thiago Custodio
Thiago Custodio

Reputation: 18387

oracle where clause with case when

I'm trying to avoid dynamic sql. I need to check a value from a parameter, and depending on it, apply the right clause.

ex:

select * from table
WHERE 1 =
CASE
    WHEN channel = 1 AND REGEXP_LIKE(LOGIN, '[[:digit:]]') THEN 1--only numbers
    WHEN channel = 2 AND LOGIN LIKE 'MI|%' THEN 1
    WHEN channel = 3 AND LOGIN NOT LIKE 'MI|%' AND NOT REGEXP_LIKE(LOGIN, '^\d+(\.\d+)?$', '') THEN 1 --except what is being filtered on case 1 and 2
    ELSE 0
END

Sample DATA from LOGIN column:

VIC.A67923
2013836
257551
GAB.A53272
MI|1234
MI|5345

Running the filters individually, they are working fine, but testing assigning the proper value to the parameter, it's returning wrong data.

e.g. when passing 1, it should return only those that have numbers, but it's returning data that has text on it too.

Upvotes: 0

Views: 581

Answers (2)

D Stanley
D Stanley

Reputation: 152556

Change your case to

CASE
    WHEN channel = 1 AND REGEXP_LIKE(LOGIN, '[[:digit:]]') 
        THEN 1--only numbers
    WHEN channel = 2 AND LOGIN LIKE 'MI|%' 
        THEN 2
    WHEN channel = 3 AND LOGIN NOT LIKE 'MI|%' AND NOT REGEXP_LIKE(LOGIN, '^\d+(\.\d+)?$', '') 
        THEN 3 --except what is being filtered on case 1 and 2
    ELSE 4
END

or perhaps, if channel is an input parameter:

WHERE channel =
    CASE
        WHEN REGEXP_LIKE(LOGIN, '[[:digit:]]') 
            THEN 1--only numbers
        WHEN LOGIN LIKE 'MI|%' 
            THEN 2
        WHEN LOGIN NOT LIKE 'MI|%' AND NOT REGEXP_LIKE(LOGIN, '^\d+(\.\d+)?$', '') 
            THEN 3 --except what is being filtered on case 1 and 2
        ELSE 4
    END

Upvotes: 1

Brian Leach
Brian Leach

Reputation: 2101

You didn't anchor and repeat your digit check. Anchor it to the beginning and end of the string. And all of your cases return 1, so it ends up doing nothing

SELECT *
  FROM tablea
 WHERE 1 = CASE WHEN channel = 1 AND REGEXP_LIKE (login, '^[[:digit:]]*$')
               THEN 1                                                                                      --only numbers
               WHEN channel = 2 AND login LIKE 'MI|%'
               THEN 1
               WHEN channel = 3 AND login NOT LIKE 'MI|%' AND NOT REGEXP_LIKE (login, '^\d+(\.\d+)?$', '')
               THEN 1                                                     --except what is being filtered on case 1 and 2
               ELSE 1
           END

Upvotes: 3

Related Questions