Case(IF) Invalid Expression

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.

Frank Kulash11111

Hi,

3609739 wrote: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'...

So, you want to get the same results without using regular expressions; is that the problem?

What is the '?' supposed to do in the expressions above?  It seems that REGEXP_LIKE will return the same results with or without the '?'s above.

TRANSLATE ( UPPER (user_name)
          , 'ABCDEFGHIJKLMNOPQRSTUVWXY012345678'
          , 'ZZZZZZZZZZZZZZZZZZZZZZZZZ999999999'
          )  = 'ZZZZZ99'

will be true if (and only if) user_name consists of exactly 5 letters (case insensitive), followed by exactly 2 digits.

TRANSLATE ( SUBSTR (user_name, -9)
          , '012345678'
          , '999999999'
          )  = '999999999'

will be true is (and only if) user_name ends with exactly 9 digits.  (It doesn't matter what, if anything, comes before those 9 digits.)

Gianni Ceresa

Keep in mind ODV (and all the related tools) do not "speak" SQL like an Oracle Database would do. It uses a kind of logical SQL (LSQL) because it must then translate this LSQL into all the possible sources of data it supports (so from a database to Excel etc.).

So not all the SQL functions that DB known exists.

I don't have ODV, but I suppose it's the same as DVD, so the supported and known functions are all listed in the window where you can enter that CASE WHEN.

Peter Lord

Thanks for the great suggestion. I think the problem with the statement lies with the REGEXP_LIKE statement that ODV does not recognise.

Is there something that replaces this?