Oracle查询因添加了COLLATE子句而失败

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

但这一条失败了，因为有了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： 5月11 21:03:20 2020日周一发布12.2.0.1.0产品

版权所有（c）1982年，2017年，Oracle。版权所有。

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

连接到：Oracle Database 12c Enterprise Edition 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

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

"_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';

Answer 2

一元操作符COLLATE仅在Oracle 12.2中引入，因此会出现错误，因为您的版本是12.1。

为什么会出现错误“FROMkeyword not found where expected”（关键字未在预期的位置找到），以及您在哪里找到它？因为在Oracle 12.1中，COLLATE一词没有特殊含义。在SELECT子句中的列名后找到它，Oracle认为它是该列的别名。由于这个别名（它认为它是别名）后面没有逗号，因此需要选择其他表达式，因此它需要关键字FROM。它找到的是拉丁文_AI（或者你最初尝试过的任何东西），这就是它抛出错误的地方——它希望正好在那个地方。

12.1中的变通方法？有一个，但它有缺点。COLLATE的好处是可以在查询中指示归类，而不会影响整个会话。这与在查询中调用TO_DATE时使用日期格式模型相同，而无需更改NLS_DATE_FORMAT。

但是，在12.1中，您不能在查询级别控制归类；您必须为会话更改它（如果您在会话中执行的其他查询或其他操作需要不同的归类，则将其更改回来）。你要跑才能做到

alter session set nls_sort = 'LATIN_AI';

在运行查询之前。

如果在运行此查询后需要恢复到“old”nls\u排序，那么在将其更改为“LATIN\u AI”之前，最好先了解它是什么。您可以通过在上面显示的alter会话之前运行以下查询来实现这一点。

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

然后，完成查询后，需要再次运行alter session，将nls\u sort更改回旧值。

旁注-为什么要将日期与where子句中的字符串进行比较？（或者是字符串数据类型中的日期的——这是一种更糟糕的做法？）