添加了COLLATE子句，Oracle查询失败

Question

在sqlplus中，此查询工作：

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这一次失败，并使用collate子句：

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

我该怎么做才能解决这个问题？

更新:在答案中尝试了建议的查询。得到

SQL*Plus:发布12.2.0.1.0版，5月11日21:03:20 2020

版权(c) 1982,2017，Oracle。版权所有。

输入密码:最后成功登入时间:星期一2020年5月11日16:51:34 -04:00

连接到: Oracle数据库12c企业版发行版12.1.0.2.0 -64位产品，包括分区、实时应用程序集群、自动存储管理、OLAP、高级分析和真实应用程序测试选项

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

一元操作符COLLATE仅在Oracle12.2中引入，因此您会得到错误，因为您的版本为12.1。

为什么您得到的错误"FROM关键字找不到预期的地方“，以及您在哪里找到它？因为在Oracle12.1中，单词COLLATE没有特殊的含义。在SELECT子句中的列名之后找到它，甲骨文认为它是列的别名。而且，由于这个别名(无论如何，它认为它是别名)后面没有逗号以供其他表达式选择，所以它需要关键字FROM。它会找到LATIN_AI (或者您最初尝试过的任何东西)和，这是它抛出错误的--它正期待着FROM出现在这个位置。

在12.1中解决问题？有一个，但也有缺点。COLLATE的好处是可以在查询中指示排序规则，而不会影响整个会话。与在查询中调用TO_DATE时使用日期格式模型相同，而不必更改NLS_DATE_FORMAT。

唉，在12.1中，您无法在查询级别控制排序规则；您必须为会话更改排序规则(如果您在会话中执行的其他查询或其他事情需要不同的排序规则，则必须将它更改回)。你这样做是通过跑步

alter session set nls_sort = 'LATIN_AI';

在运行查询之前。

如果您需要在运行此查询后恢复到“旧”nls_sort，您最好先知道它是什么，然后在之前将其更改为‘拉丁语_AI’。要做到这一点，可以在之前运行以下查询alter session，如上面所示的alter session。

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

然后，在您完成查询之后，您将需要再次运行alter session来将nls_sort更改为旧值。

附带注意--为什么要将日期与where子句中的字符串进行比较？(或者as_of_date是字符串数据类型-这是一种更糟糕的做法吗？)

Answer 2

在使用排序规则时，有三个后缀可以改变排序和比较的行为。

"_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.

虽然不确定上述使用_general的目的。但是，只有使用LATIN_xx，用上述任何选项替换xx必须完成您的工作。

查询以检查拉丁文的有效NLS_SORT值:-

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

附加信息：Oracle中的排序规则

摘要查询：

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