CODEWITHSUNDEEP
sqloracle-database
Ryan_W4588
Ryan_W4588

Reputation: 668

ORA-00920: invalid relational operator

In a database, I am trying to pull information that is later than a specified date. I should note beforehand that the date is in an odd format: YYYYMMDDHH24MISS## where ## is a two letter string which defines something useless to my query. Thus, I am using substr to just remove them.

My query, below, throws the following error, and I canot find out why:

[Error Code: 920, SQL State: 42000] ORA-00920: invalid relational operator

My Query:

SELECT *
  FROM table_name
 WHERE to_date(substr(COLUMN_NAME,1,14), 'YYYYMMDDHH24MISS')) >=
       to_date('MIN_DATE', 'YYYYMMDDHH24MISS')

I have checked to make sure the dates are being defined correctly, and they are.

Example of what I have used for MIN_DATE is: 20140101000000

Upvotes: 11

Views: 148277

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You get this error in Oracle when you are missing a comparison operation, such as = -- as John Maillet already noted.

My concern is the second part of the where clause:

where to_date(substr(COLUMN_NAME, 1, 14), 'YYYYMMDDHH24MISS') >=
                  to_date('MIN_DATE', 'YYYYMMDDHH24MISS')

You have MIN_DATE in single quotes. This is interpreted as a string with eight letters in it, starting with 'M' and ending with 'E'. This is not interpreted as a variable. Presumably you mean:

where to_date(substr(COLUMN_NAME, 1, 14), 'YYYYMMDDHH24MISS') >=
                  to_date(MIN_DATE, 'YYYYMMDDHH24MISS')

You should only use single quotes for string and date constants.

I should add that you should be able to do this comparison without having to convert to dates:

where left(COLUMN_NAME, 14) = MIN_DATE

Upvotes: 9

John Maillet
John Maillet

Reputation: 710

You have an extra parenthesis at the end of the first to_date

Upvotes: 21

Related Questions