CODEWITHSUNDEEP
sqloracle-database
kumar shivam
kumar shivam

Reputation: 69

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

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.

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

Upvotes: 3

Views: 46009

Answers (3)

Omer Gurarslan
Omer Gurarslan

Reputation: 1027

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

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 IN instead of ( = ) and NOT IN instead of ( != )
  3. Handle multiple values by using ALL, ANY or SOME as documented.

Upvotes: 0

MT0
MT0

Reputation: 168671

You are missing 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_DATE function and assumes the final line is trying to compare F.TRANS_DT with one (ANY) or both (ALL) the values ('08/30/2017','mm/dd/yyyy') so is assuming the query should have the syntax:

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.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Just use the date keyword and ISO constants:

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.

Upvotes: 3

Related Questions