Re: Error with outer join

On 7 13 , 7 45 , Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Jul 13, 4:06 am, FatP <thats..._at_gmail.com> wrote:
>
>
>
> > It seemsOracleis a bit over sensitive when something happen in the
> > column used forouterjoin. 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 theouter
> > join.
>
> > Another strange thing is that the order of operands in arithematic
> > operations affectsouterjoin
>
> > 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
> >outerjoin 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:
> >OracleDatabase 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
>
> Rob van Wijk has supplied the correct answer for you.
>
> This behavior caused me a bit of confusion when I first started
> working withouterjoins, but after a short amount of time, it starts
> to make sense. Just so that we are not mislead, let's add a row to
> table T2:
> INSERT INTO T2 VALUES (10);
>
> select * from t1, t2
> where t1.n1 (+) = t2.n2;
>
> N1 N2
> ---------- ----------
> 10
>
> Now, a quick check to see how NULL mathematics might work:
> SELECT
> NULL+1
> FROM
> DUAL;
>
> NULL+1
> ----------
> (null result returned)
>
> Let's try anouterjoin between the two tables:
> SELECT
> T1.N1,
> T2.N2
> FROM
> T1,
> T2
> WHERE
> T1.N1(+)+1=T2.N2;
>
> N1 N2
> ---------- ----------
> 10
>
> Let's try again, this time moving the 1 in front of the column:
> SELECT
> T1.N1,
> T2.N2
> FROM
> T1,
> T2
> WHERE
> 1+T1.N1(+)=T2.N2;
>
> N1 N2
> ---------- ----------
> 10
>
> Let's try again, this time moving the 1 to the other side of the equal
> sign:
> SELECT
> T1.N1,
> T2.N2
> FROM
> T1,
> T2
> WHERE
> T1.N1(+)=T2.N2-1;
>
> N1 N2
> ---------- ----------
> 10
>
> Things to be careful with:
> SELECT
> T1.N1,
> T2.N2
> FROM
> T1,
> T2
> WHERE
> NVL(T1.N1+1,T2.N2)=T2.N2;
>
> no rows selected
>
> Things to be careful with:
> SELECT
> T1.N1,
> T2.N2
> FROM
> T1,
> T2
> WHERE
> NVL2(T1.N1,T1.N1+1,T2.N2)=T2.N2;
>
> no rows selected
>
> You have a couple syntax examples above to choose from, be certain to
> check the performance of the selected syntax.
>
> Charles Hooper
> IT Manager/OracleDBA
> K&M Machine-Fabricating, Inc.

So the (+) acts on an individual column, not the overall result on a side. That's why

select * from t1, t2
where 1+t1.n1 (+)= t2.n2

is OK, but

select * from t1, t2
where t1.n1+1 (+)= t2.n2

fails. And that's why a parenthesis causes errors.

Thanks Hooper for interesting point.

FatP Received on Sat Jul 14 2007 - 12:13:07 CDT