ORA-01719 outer join operator (+) not allowed in operand of OR or IN

srikotesh
srikotesh used Ask the Experts™
on
GETTING ORA-01719 WHEN I USE OR or IN CONDITIONS

SELECT * FROM summary_trans trans,doc_hdr hdr
WHERE trans.doc_number = hdr.doc_no (+)
AND hdr.filler_107 NOT IN ('TRA','CON')
OR hdr.filler_107 IS NULL;

WHAT IS THE ALTERNATIVE FOR THIS QUERY APART FROM UNION ALL CASE
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The "(+)" notation/syntax is Oracle proprietary; so you need to rewrite that statement using ANSI SQL joins, then you're able to do what you want ;-)
Something like this (untested):
select *
  from summary_trans trans
 right outer join doc_hdr hdr
    on (hdr.doc_no = trans.doc_number)
 where hdr.filler_107 not in ('TRA', 'CON')
    or hdr.filler_107 is null;

Open in new window

Helena Markováprogrammer-analyst

Commented:
I think that this ought to work for you:
SELECT * FROM summary_trans trans
LEFT OUTER JOIN doc_hdr hdr on trans.doc_number = hdr.doc_no where hdr.filler_107 NOT IN ('TRA','CON') OR hdr.filler_107 IS NULL;

Author

Commented:
HI Experts,

i have to use ANSI TYPE JOIN ONLY
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

i have to use ANSI TYPE JOIN ONLY
The 2 SQL statements from Helena and me ARE ANSI joins! Yours is NOT!
Helena Markováprogrammer-analyst

Commented:
Both examples are ANSI JOINS.

Author

Commented:
Sorry
i have to use + operator,legacy join
i have to use + operator,legacy join
Again: the (+) operator is not "legacy", it's just Oracle's shortcut for simple left and right joins!
And secondly: why do you have to use this?!? ANSI SQL (thus our suggested joins) is available in virtually any relational database!
Helena Markováprogrammer-analyst

Commented:
Why ?

Author

Commented:
whatever I have written the query is a sample query.
actual query having many tables and using + notation

I don't want to change entire query
I don't want to change entire query
But you'll have to in order to achieve what you want; there's no way around it! Oracle's (+) is very restricted when it comes to more complex queries!
Btw: it would be more than just bad practise to add a UNION or whatever kind of SQL construct just to have those (+) operands!
Mark GeerlingsDatabase Administrator

Commented:
Besides the syntax restrictions or limitations, your query also has a logic problem (that may be one of the reasons that Oracle doesn't allow this syntax).  Look at this part of your query:
WHERE trans.doc_number = hdr.doc_no (+)
 AND hdr.filler_107 NOT IN ('TRA','CON')

Open in new window


If the hdr table actually has no matching record for a particular  trans.doc_number, how can Oracle evaluate the value in a column of a non-existent record to see if that is either in (or not in) your bind variable values?
johnsoneSenior Oracle DBA

Commented:
Mark,

I believe the correct syntax to do that is:
WHERE trans.doc_number = hdr.doc_no (+)
 AND hdr.filler_107(+) NOT IN ('TRA','CON')

Open in new window

Been a while since I used the old syntax, but I think that is correct.
Database Administrator
Commented:
No, I believe that would have to be more like this:

WHERE trans.doc_number = hdr.doc_no (+)
 AND nvl(hdr.filler_107,'xxyyzz') NOT IN ('TRA','CON')

Open in new window


Whatever value is provided as the default value for "nvl"  here *MUST* be a value that *DOES NOT* exist as a valid value in any existing records in this table.  The particular string: 'xxyyzz' in my example, is a value I often use with nvl on varchar2 columns, since I have never encountered that string as a valid value in any system I have worked in.
johnsoneSenior Oracle DBA

Commented:
This is from the Oracle 7 doc:

The outer join extends the result of a simple join. An outer join returns
all rows that satisfy the join condition and those rows from one table
for which no rows from the other satisfy the join condition. Such rows
are not returned by a simple join. To write a query that performs an
outer join of tables A and B and returns all rows from A, apply the
outer join operator (+) to all columns of B in the join condition. For all
rows in A that have no matching rows in B, Oracle7 returns NULL for
any select list expressions containing columns of B.
This is the basic syntax of an outer join of two tables:
SELECT
table1.column (+) = table2.column
table
.column
,
WHERE
FROM table1, table2
table1.column = table2.column (+)
Outer join queries are subject to the following rules and restrictions:
• The (+) operator can only appear in the WHERE clause, not in
the select list, and can only be applied to a column of a table or
view.
• If A and B are joined by multiple join conditions, the (+) operator
must be used in all of these conditions.
• The (+) operator can only be applied to a column, rather than to
an arbitrary expression, although an arbitrary expression can
contain a column marked with the (+) operator.
• A condition containing the (+) operator cannot be combined with
another condition using the OR logical operator.
• A condition cannot use the IN comparison operator to compare a
column marked with the (+) operator to another expression.
• A condition cannot compare a column marked with the (+)
operator to a subquery.
The important part of that is this:
apply the outer join operator (+) to all columns of B in the join condition
Since the HDR aliased table is the outer joined to table, the (+) must occur on all conditions on that alias in the where clause.  I recall that being an issue a very long time ago, but honestly I don't recall the situation.  It has something to do with the null being returned and it will try to evaluate it, but putting the (+) tells it to ignore that test when there is no joined row.

Anyway, the relevant bullet point to this question is really this one:
• A condition containing the (+) operator cannot be combined with
another condition using the OR logical operator.
Done.  Taken directly from the 7.3.4 documentation.  Fully documented that you cannot do that.  If you need an OR condition, you need to use the ANSI join style.

Author

Commented:
THANKS

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial