ORA-00920: invalid relational operator

Example 1

create table tq84_a (
   id  integer,
   txt varchar2(10),
   val number(5,2)
);


begin
insert into tq84_a values(1, 'abc', 1.11);
insert into tq84_a values(2, 'def', 2.22);
insert into tq84_a values(1, 'ghi', 4.00);
insert into tq84_a values(3, 'jkl', 3.99);
insert into tq84_a values(2, 'def', 7.07);
insert into tq84_a values(2, 'abc', 4.50);
end;
/

The following select statment throws ORA-00920: invalid relational operator:

select * from tq84_a where  id, txt  in ( (1, 'ghi'), (1,'def'));

This statement runs OK:

select * from tq84_a where (id, txt) in ( (1, 'ghi'), (1,'def'));

Cleaning up:

drop table tq84_a;

Example 2

A simple table:

create table tq84_ora_00920 (
    id  integer   generated always as identity,
    prt varchar2( 3),
    txt varchar2(20)
);

The following SQL statement is syntactically incorrect: it ends with a comma. When trying to execute it, Oracle will throw ORA-00936: missing expression.

select
   max(id),
   prt
from
   tq84_ora_00920
group by
   prt,
;

However, a select statement that uses same query in a subquery will throw ORA-00920: invalid relational operator:

select
   prt,
   txt
from
   tq84_ora_00920
where
  (id, prt) in (
     select
        max(id),
        prt
     from
        tq84_ora_00920
     group by
        prt,     -- <=== ORA-00920: invalid relational operator
   );

Cleaning up:

drop table tq84_ora_00920;

ORA-00920: invalid relational operator

Example 1

Example 2

See also