| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Error with outer join
On Jul 13, 4:06 am, 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
Rob van Wijk has supplied the correct answer for you.
This behavior caused me a bit of confusion when I first started
working with outer joins, 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
Let's try an outer join 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/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Fri Jul 13 2007 - 06:45:22 CDT
 |
 |