Oracle Analytics and AI

Peter Lord

Summary

Case(IF) Invalid Expression

Content

Hi, can anyone tell me why the following Case(IF) returns and invalid expression response?

CASE

WHEN (CLIENT_TYPE = 'ORGTCJ' AND REGEXP_LIKE (User_name, '^[A-Z]{5}[0-9]{2}?$','i')) THEN 'ALFIE'

WHEN (CLIENT_TYPE = 'ORGTCJ' AND USER_NAME IN ('AXO97', 'AAC57')) THEN 'ALFIE'

WHEN (CLIENT_TYPE = 'ORGTCJ' AND REGEXP_LIKE (User_name, '[0-9]{9}?$','i')) THEN 'LTDS'

WHEN (CLIENT_TYPE = 'ORGTCJ' AND (User_name = 'autofix123')) THEN 'AUTOFIXEMP'

WHEN CLIENT_TYPE = 'ROLAND' THEN 'ROLAND'

WHEN CLIENT_TYPE = 'LTDS' THEN 'LTDS'

WHEN CLIENT_TYPE = 'ALFIE' THEN 'ALFIE'

WHEN CLIENT_TYPE = 'CSS_STI' THEN 'CSS_STI'

WHEN CLIENT_TYPE = 'AUTOFIXEMP' THEN 'AUTOFIXEMP'

WHEN CLIENT_TYPE = 'RABIT' THEN 'RABIT'

WHEN CLIENT_TYPE = 'MPF_TEST_TOOL' THEN 'WOOSH'

WHEN CLIENT_TYPE = 'BACK_OFFICE' THEN 'WOOSH'

WHEN CLIENT_TYPE = 'FRONT_OFFICE' THEN 'WOOSH'

WHEN CLIENT_TYPE = 'FLOW_OR' THEN 'FLOW'

WHEN CLIENT_TYPE = 'MPF_TECH_USER' THEN 'WOOSH'

WHEN CLIENT_TYPE = 'ORSIEBEL' THEN 'ORSIEBEL'

WHEN CLIENT_TYPE = 'REDCARE' THEN 'REDCARE'

WHEN CLIENT_TYPE = 'WLRFLOW' then 'FLOW'

WHEN CLIENT_TYPE = 'AIB' THEN 'AIB'

ELSE 'WOOSH'

END

Frank Kulash11111

Hi,

The CASE expression by itself looks okay.  I'll bet the problem is how you're using the CASE expression.   Show enough of the context so anybody can re-produce the problem.

If you ever have a question about a SQL feature (such as CASE expressions), then you should post in in the SQL and PL/SQL Forum:

By the way, the CASE expression can be simplified a little

WHEN CLIENT_TYPE = 'ROLAND' THEN 'ROLAND' 
WHEN CLIENT_TYPE = 'LTDS' THEN 'LTDS'
WHEN CLIENT_TYPE = 'ALFIE' THEN 'ALFIE' 
WHEN CLIENT_TYPE = 'CSS_STI' THEN 'CSS_STI'
WHEN CLIENT_TYPE = 'AUTOFIXEMP' THEN 'AUTOFIXEMP' 
WHEN CLIENT_TYPE = 'RABIT' THEN 'RABIT'
WHEN CLIENT_TYPE = 'MPF_TEST_TOOL' THEN 'WOOSH' 
WHEN CLIENT_TYPE = 'BACK_OFFICE' THEN 'WOOSH'
WHEN CLIENT_TYPE = 'FRONT_OFFICE' THEN 'WOOSH' 

can be re-written as

WHEN CLIENT_TYPE IN ( 'ROLAND'
                    , 'LTDS'
                    , 'ALFIE'
                    , 'CSS_STI'
                    , 'AUTOFIXEMP'
                    , 'RABIT'
                    )       THEN  CLIENT_TYPE
WHEN CLIENT_TYPE IN ( 'MPF_TEST_TOOL'
                    , 'BACK_OFFICE'
                    , 'FRONT_OFFICE'
                    )       THEN 'WOOSH' 

Both get the same results, equally fast, but I find the latter way much easier to write, read, debug and maintain.