oracle ORA-01797: 此运算符后必须跟 ANY 或 ALL 错误

ORA-01797: this operator must be followed by ANY or ALL error

提问by kumar shivam

While I am executing the query,

当我执行查询时，

select *
from file_log f
where F.DCP_SRCE_FILENM_FK in ('ABC','DEF') and
      F.TRANS_DT>=to_date('08/25/2017','mm/dd/yyyy') and
      F.TRANS_DT<=('08/30/2017','mm/dd/yyyy')

am getting the following error:

我收到以下错误：

ORA-01797: this operator must be followed by ANY or ALL.

ORA-01797: 此运算符后必须跟 ANY 或 ALL。

Could you all please help me in writing the proper query so that this error would go?

你们能帮我写出正确的查询，以便这个错误消失吗？

回答by Gordon Linoff

Just use the datekeyword and ISO constants:

只需使用date关键字和 ISO 常量：

select *
from file_log f
where F.DCP_SRCE_FILENM_FK in ('ABC','DEF') and
      F.TRANS_DT >= date '2017-08-25' and
      F.TRANS_DT <= date '2017-08-30';

You are getting the error because the second constant is missing to_date(). But you might as well use the proper syntax for a date constant.

您收到错误是因为缺少第二个常量to_date()。但是您不妨为​​日期常量使用正确的语法。

回答by MT0

You are missing TO_DATE:

你失踪了TO_DATE：

select *
from file_log f
where F.DCP_SRCE_FILENM_FK in ('ABC','DEF') and
      F.TRANS_DT>=to_date('08/25/2017','mm/dd/yyyy') and
      F.TRANS_DT<=TO_DATE('08/30/2017','mm/dd/yyyy')     -- Missing on this line

Why it is throwing that exception:

为什么抛出该异常：

The SQL parser cannot discern that you intended to use a TO_DATEfunction and assumes the final line is trying to compare F.TRANS_DTwith one (ANY) or both (ALL) the values ('08/30/2017','mm/dd/yyyy')so is assuming the query should have the syntax:

SQL 解析器无法识别您打算使用TO_DATE函数并假设最后一行试图F.TRANS_DT与一个 ( ANY) 或两个 ( ALL) 值进行比较，('08/30/2017','mm/dd/yyyy')因此假设查询应具有以下语法：

select *
from file_log f
where F.DCP_SRCE_FILENM_FK in ('ABC','DEF') and
      F.TRANS_DT>=to_date('08/25/2017','mm/dd/yyyy') and
      F.TRANS_DT<= ANY ('08/30/2017','mm/dd/yyyy')

Which is a syntactically valid query. It would not, however, execute as trying to parse the F.TRANS_DT <= 'mm/dd/yyyy'comparison will result in trying to implicitly convert the string on the right-hand side to a date which is almost certain to fail with ORA-01858: a non-numeric character was found where a numeric was expected. But the SQL parser has done its best to suggest what is missing to make the query valid.

这是一个语法上有效的查询。但是，它不会执行，因为尝试解析F.TRANS_DT <= 'mm/dd/yyyy'比较将导致尝试将右侧的字符串隐式转换为几乎肯定会失败的日期ORA-01858: a non-numeric character was found where a numeric was expected。但是 SQL 解析器已尽力建议缺少的内容以使查询有效。

回答by Omer Gurarslan

For those who end up searching for ORA-01797error:

对于那些最终搜索ORA-01797错误的人：

This error appears when right side of a logical operator ( =, !=, >, <, <=, >= ) contains multiple values.

当逻辑运算符（=、!=、>、<、<=、>=）的右侧包含多个值时，会出现此错误。

Possible solutions:

可能的解决方案：

1. Make sure right hand side of the logical operator contains a single value.
2. Handle multiple values by using INinstead of ( = ) and NOT INinstead of ( != )
3. Handle multiple values by using ALL, ANYor SOMEas documented.

1. 确保逻辑运算符的右侧包含单个值。
2. 使用IN代替 ( = ) 和 NOT IN代替 ( != )处理多个值
3. 使用ALL，ANY或SOME按照文档处理多个值。