Re: Error with outer join

On 13 jul, 10:06, FatP <thats..._at_gmail.com> wrote:
> It seems Oracle is a bit over sensitive when something happen in the
> column used for outer join. See the following example
>
> SQL> create table t1 (n1 number);
>
> Table created.
>
> SQL> create table t2 (n2 number);
>
> Table created.
>
> SQL> select * from t1, t2
> 2 where t1.n1 (+) = t2.n2;
>
> no rows selected
>
> SQL> select * from t1, t2
> 2 where (t1.n1+1) (+)= t2.n2;
> where (t1.n1+1) (+)= t2.n2
> *
> ERROR at line 2:
> ORA-00920: invalid relational operator
>
> SQL> select * from t1, t2
> 2 where (t1.n1) (+)= t2.n2;
> where (t1.n1) (+)= t2.n2
> *
> ERROR at line 2:
> ORA-00920: invalid relational operator
>
> It seems to reject whatever containing the parenthesis in the outer
> join.
>
> Another strange thing is that the order of operands in arithematic
> operations affects outer join
>
> SQL> select * from t1, t2
> 2* where 1+t1.n1 (+)= t2.n2
> SQL> /
>
> no rows selected
>
> SQL> select * from t1, t2
> 2* where t1.n1+1 (+)= t2.n2
> SQL> /
> where t1.n1+1 (+)= t2.n2
> *
> ERROR at line 2:
> ORA-00920: invalid relational operator
>
> I know that there are workarounds for this issue, e.g., using ANSI
> outer join or create an inline view. I just want to know whether this
> is by design, and whether there's any more reasonable workaround. It
> also surprised me that this is not noticed by many people(I tried 9i
> and 10g), as it seems quite common to do some 'pre-processing' before
> join.
>
> System information:
> Oracle Database 10g Release 10.2.0.1.0 - Production
> PL/SQL Release 10.2.0.1.0 - Production
> CORE 10.2.0.1.0 Production
> TNS for Linux: Version 10.2.0.1.0 - Production
> NLSRTL Version 10.2.0.1.0 - Production
>
> Linux: RHEL4

It's all in the manual:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#sthref3175

#
The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
# Received on Fri Jul 13 2007 - 06:23:07 CDT