Wednesday, June 04, 2008

ORA-00920: invalid relational operator

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
ORA-00920: invalid relational operator
Although this error is caused most likely by trivial errors, actually it gives me a chance to talk about some SQL operators whose real meaning and side effects, in my humble opinion, are everything but clearly documented.

Funnily enough, it seems that this error message has been removed altogether from the official documentation starting from version 10R1, but this doesn't mean it cannot be returned!

Let's start then from the error:

select * from user_objects
where (object_name, object_type) =
(
('PLSQL_PROFILER_RUNS', 'TABLE'),
('PLSQL_PROFILER_UNITS', 'TABLE'),
('PLSQL_PROFILER_DATA', 'TABLE')
);

ORA-00920: invalid relational operator
The problem is in that we cannot perform a comparison in this fashion because on the right hand side of the operator there isn't a single value but a list of values (a list of lists in this case).

However we can easily fix the SQL statement in this way
select * from user_objects
where (object_name, object_type) = ANY
(
('PLSQL_PROFILER_RUNS', 'TABLE'),
('PLSQL_PROFILER_UNITS', 'TABLE'),
('PLSQL_PROFILER_DATA', 'TABLE')
);
Now, if you have the PL/SQL profiler objects installed in your schema, you should get three rows as result of this query, otherwise you'll get none, but in any case, no syntax errors will be returned.

Note also that the query above is perfectly equivalent to:
select * from user_objects
where (object_name, object_type) IN
(
('PLSQL_PROFILER_RUNS', 'TABLE'),
('PLSQL_PROFILER_UNITS', 'TABLE'),
('PLSQL_PROFILER_DATA', 'TABLE')
);
Personally i tend to write SQL statements preferably using the IN clause rather than = ANY.

Now, instead of using a fixed set of allowed values, let's write a more generic query :

select * from user_objects
where (object_name) = SOME (
select t.table_name
from user_tables t
where t.table_name like 'PLSQL%');
SOME is synonym of ANY, just in case you wonder what's the difference between the two operators.
Again, if the PL/SQL profiler tables are present, at least three rows will be returned.

If we change the operator in this way:
select * from user_objects
where (object_name) = ALL (
select t.table_name
from user_tables t
where t.table_name like 'PLSQL%');
the query will return no rows if the PL/SQL profiler tabs are present, but it will return all user objects if they are not present in your schema.
This means that = ALL behaves like != ALL when the subquery returns no rows!
On the other hand != ALL is perfectly equivalent to NOT IN.

I can't really find = ALL suitable for any purpose off the top of my mind in this moment, i'd say that ALL is most useful when combined with operators like >= or <. For instance, it can be certainly useful to have the possibility of writing queries like:
select * from user_objects
where created > ALL
(select o.created
from user_objects o
where o.object_name like 'PLSQL%');
This query means return all objects newer than the newest object whose name begins with 'PLSQL%'.
By the way, replacing ALL with ANY (or SOME), means finding all objects newer than the oldest object named PLSQL%)

The task of finding out whether the query above is more efficient than the query given below is left to the reader as exercise :-)
select * from user_objects
where created >
(select max(o.created)
from user_objects o
where o.object_name like 'PLSQL%');
Bear in mind however that when the subquery returns no rows (no objects named PLSQL%), the former query will match all user objects, while the latter none and this will certainly have some impact on your application.

See message translations for ORA-00920 and search additional resources.

3 comments:

bmaphill said...

Dude I love the information you put here. I have no idea about oracle. I am working on a program that has an oracle db behind it and have to write reports for it. I get multiple issues and so far this is the only thing on the net that has helped me work the ORA errors out. Thanks again...

ashwanth said...

Hi,

I am trying to extract only those rows which contain alphanumeric
values,but i get invalid relational operator,What could be possible reason?


create table alpha_numeric(col1 varchar2(20));


insert into alpha_numeric values ('1000');
insert into alpha_numeric values ('a1093b');
insert into alpha_numeric values ('19b45');
insert into alpha_numeric values ('231');
insert into alpha_numeric values ('1000cc');
insert into alpha_numeric values ('a1000');
commit;

select * from alpha_numeric
where translate(col1,'1234567890',' ');


Byte64 said...

TRANSLATE is a function, not a relational operator, you need to modify your WHERE clause to something like:
select * from alpha_numeric
where translate(col1,'1234567890',' ') != col1;

or whatever you want to achieve.

Flavio

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles