Error Description
The following queries fails with ORA-920.
Case 01:
SQL> select r.pack_id r,p.rate_id e from rate_to_send r
2 JOIN package_rate p ON(r.pack_id.p.rate_id);
JOIN package_rate p ON(r.pack_id.p.rate_id)
*
ERROR at line 2:
ORA-00920: invalid relational operator
A variant of ORA-920 is below.
Case 02:
SQL> select table_name from user_tables where (table_name, tablespace_name)=
2 (('TEST1','USERS'), ('TEST2','USERS'));
(('TEST1','USERS'), ('TEST2','USERS'))
*
ERROR at line 2:
ORA-00920: invalid relational operator
Cause of the Error
ORA-00920 is occurred if there is invalid or missing relational operator between JOIN condition or within WHERE clause.
Solution of the Problem
In the case 01, within JOIN condition no equal (=) sign is there. Relation operator is not present there and hence error occurred. Valid relational operators are =, !=, ^=, <>, >, <, >=, <=, ALL, ANY, [NOT] BETWEEN, EXISTS, [NOT] IN, IS [NOT] NULL, or [NOT] LIKE in the condition. So ensure that in the condition no other relation operator is present without these operators. Following would correct the problem.
SQL> select r.pack_id r,p.rate_id e from rate_to_send r
2 JOIN package_rate p ON(r.pack_id=p.rate_id);
no rows selected
In the case 02, same error returned because oracle does not permit to compare values in this way. If there is equal operator then on the right hand side of the operator there is needed a single value. A list of values are not permitted.
You can fix the case 02 statement by simply adding ANY clause after the equal sign.
SQL> select table_name from user_tables where (table_name, tablespace_name)=ANY
2 (('TEST1','USERS'), ('TEST2','USERS'));
TABLE_NAME
------------------------------
TEST2
TEST2 table resides under USERS tablespace.
Alternatively you can use IN clause instead of (=ANY).
SQL> select table_name from user_tables where (table_name, tablespace_name) IN
2 (('TEST1','USERS'), ('TEST2','USERS'));
TABLE_NAME
------------------------------
TEST2
Related Documents
No comments:
Post a Comment