CODEWITHSUNDEEP
sqldatabaseoracle-databasecase-when
Artm94
Artm94

Reputation: 1

Oracle error 43914 in CASE WHEN expression: arguments have undefined collation

I want to know which is the difference between this query:

SELECT * FROM TEST_TABLE 
WHERE CHAR_FIELD = CASE 
                      WHEN KEY_FIELD = 'Q' 
                      THEN 'M' 
                      ELSE 
                         CASE 
                            WHEN KEY_FIELD = 'T' 
                            THEN 'K'
                            ELSE CHAR_FIELD2 
                         END 
                   END;

And this:

SELECT * FROM TEST_TABLE 
WHERE CHAR_FIELD = CASE 
                      WHEN KEY_FIELD = 'Q' 
                      THEN 'M' 
                      ELSE 
                         CASE 
                            WHEN KEY_FIELD <> 'T'
                            THEN CHAR_FIELD2 
                            ELSE 'K' 
                        END 
                   END;

I expect both queries to return same data in all cases but, first one gives me an ORA-43914 "unable to determine collation: arguments have undefined collation" error while second one works just fine.

I read this information about collation determination in Oracle but it's not clear to me how it works with case-when expression.

Thanks for your help.

Upvotes: 0

Views: 681

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270683

The issue is when KEY_FIELD is NULL. In that case, the second CASE always goes to the ELSE in both queries.

That said, I find the CASE expression overcomplicates the logic. I prefer simpler boolean operations:

WHERE (CHAR_FIELD = 'M' AND KEY_FIELD = 'Q') OR
      (CHAR_FIELD = 'K' AND KEY_FIELD = 'T') OR
      (CHAR_FIELD = 'K' AND KEY_FIELD NOT IN ('Q', 'T'))

This doesn't actually handle NULL values in KEY_FIELD. That would have to be handled explicitly.

Upvotes: 2

Related Questions