Oracle query fails with added COLLATE clause

Question

In sqlplus, this query works:

SQL> SELECT DISTINCT
  2  CNTPTY_TYPE
  3  FROM visn_exp.V_IHCVSN_CERT_DEP
  4  WHERE as_of_dt = '08-may-20'
  5  ;

CNTPTY_TYPE
----------------------------------------

Retail
PSE
Non-Financial Corporate
FI

BUt this one fails, with the collate clause:

SQL> SELECT DISTINCT
  2  CNTPTY_TYPE COLLATE latin1_general_CI_AI AS CNTPTY_TYPE
  3  FROM VISN_EXP.V_IHCVSN_CERT_DEP
  4  WHERE as_of_dt = '08-may-20'
  5  ;
CNTPTY_TYPE COLLATE latin_general_CI_AI AS CNTPTY_TYPE
                    *
ERROR at line 2:
ORA-00923: FROM keyword not found where expected

What should I do to fix this?

Update: Tried the suggested query in the answer. Got

SQL*Plus: Release 12.2.0.1.0 Production on Mon May 11 21:03:20 2020

Copyright (c) 1982, 2017, Oracle. All rights reserved.

Enter password: Last Successful login time: Mon May 11 2020 16:51:34 -04:00

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT DISTINCT
  2  CNTPTY_TYPE COLLATE LATIN_AI AS CNTPTY_TYPE
  3  FROM VISN_EXP.V_IHCVSN_CERT_DEP
  4  WHERE as_of_dt = '08-may-20';
CNTPTY_TYPE COLLATE LATIN_AI AS CNTPTY_TYPE
                    *
ERROR at line 2:
ORA-00923: FROM keyword not found where expected

Answer 1

The unary operator COLLATE was introduced only in Oracle 12.2, so you get the error because your version is 12.1.

Why do you get the error "FROM keyword not found where expected", and exactly where you found it? Because in Oracle 12.1, the word COLLATE has no special meaning. Finding it after a column name in the SELECT clause, Oracle thinks it's an alias for the column. And since this alias (what it thinks it's an alias, anyway) is not followed by comma for additional expressions to select, it expects the keyword FROM. It finds LATIN_AI instead (or whatever you tried originally) and that's where it throws the error - it was expecting FROM in exactly that place.

Workaround in 12.1? There is one, but it has disadvantages. The benefit of COLLATE is that you can indicate a collation in the query, without affecting your overall session. The same as using a date format model when you call TO_DATE in the query, without having to change NLS_DATE_FORMAT.

Alas, in 12.1 you can't control the collation at the query level; you must change it for the session (and then change it back, if other queries or other things you do in the session need a different collation). You do that by running

alter session set nls_sort = 'LATIN_AI';

before you run your query.

If you need to revert to the "old" nls_sort after you run this query, you would do well to know what it is, before you change it to 'LATIN_AI'. You do that by running the following query before the alter session shown above.

select value from v$nls_parameters where parameter = 'NLS_SORT';

Then, after you are done with your query, you will need to run alter session again to change nls_sort back to the old value.

Side note - why do you compare a date to a string in the where clause? (Or is as_of_date in string data type - which is an even worse practice?)

Answer 2

When using collations there are three suffixes that alter the behaviour of sorts and comparisons.

"_CI" : Case insensitive, but accent sensitive.
"_AI" : Both case and accent insensitive.
"_CS" : Both case and accent sensitive. This is default if no extension is used.

Though not sure on the purpose of _general usage above. However only using LATIN_xx, replacing xx with any of the above option must do your job.

Query to check valid NLS_SORT Values for Latin :-

SELECT * 
FROM V$NLS_VALID_VALUES 
WHERE parameter = 'SORT' and value = 'LATIN'; 

Additional Info : Collations in Oracle

Summarized Query :

SELECT DISTINCT
CNTPTY_TYPE COLLATE LATIN_AI AS CNTPTY_TYPE
FROM VISN_EXP.V_IHCVSN_CERT_DEP
WHERE as_of_dt = '08-may-20';