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 statement 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: Syntactically wrong statement
create table tq84_ora_00920 (
id integer generated always as identity,
prt varchar2( 3),
txt varchar2(20)
);
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;
Example 3: Unrecognized function
regexp_match is not a (built-in) Oracle function so that the following statement throws the ORA-00920 error:
select
count(*) cnt
from
user_objects
where
regexp_match(object_name, '\d');
select
count(*) cnt
from
user_objects
where
regexp_like(object_name, '\d');