CODEWITHSUNDEEP
oracle-databaseoracle11g
Garfield
Garfield

Reputation: 143

ORA-00920: invalid relational operator even after no parentheses error

This query is giving me an ORA-00920: invalid relational operator error. I checked for the parenthesis but it looks perfectly fine.

DELETE FROM delete_test d
WHERE d.trans_date, d.item_nbr, d.serial_nbr IN (
SELECT b.trans_date, b.item_nbr, b.serial_nbr FROM (
SELECT a.*, RANK() OVER(PARTITION BY serial_nbr ORDER BY trans_date) rnk 
FROM delete_test a) b
WHERE rnk <> 1)
;

Can anyone help me debug it?

Upvotes: 0

Views: 862

Answers (1)

T. Peter
T. Peter

Reputation: 887

You should us extra parenthesis to specify the tuples

try this:

DELETE FROM delete_test d
WHERE (d.trans_date, d.item_nbr, d.serial_nbr) IN ((
SELECT b.trans_date, b.item_nbr, b.serial_nbr FROM (
SELECT a.*, RANK() OVER(PARTITION BY serial_nbr ORDER BY trans_date) rnk 
FROM delete_test a) b
WHERE rnk <> 1))

also after some testing if the tuples is generate by subquery you only need one pair parenthesis, but with plain text like ('a','b','c') you need two pair, but two pair always work so imo just always use two pair.

Upvotes: 3

Related Questions