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

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
LVL 2
srikoteshAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alexander Eßer [Alex140181]Software DeveloperCommented:
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

0
Helena Markováprogrammer-analystCommented:
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;
1
srikoteshAuthor Commented:
HI Experts,

i have to use ANSI TYPE JOIN ONLY
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Alexander Eßer [Alex140181]Software DeveloperCommented:
i have to use ANSI TYPE JOIN ONLY
The 2 SQL statements from Helena and me ARE ANSI joins! Yours is NOT!
0
Helena Markováprogrammer-analystCommented:
Both examples are ANSI JOINS.
1
srikoteshAuthor Commented:
Sorry
i have to use + operator,legacy join
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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!
0
Helena Markováprogrammer-analystCommented:
Why ?
0
srikoteshAuthor 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
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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!
1
Alexander Eßer [Alex140181]Software DeveloperCommented:
Btw: it would be more than just bad practise to add a UNION or whatever kind of SQL construct just to have those (+) operands!
0
Mark GeerlingsDatabase AdministratorCommented:
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?
0
johnsoneSenior Oracle DBACommented:
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.
0
Mark GeerlingsDatabase AdministratorCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
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.
3
srikoteshAuthor Commented:
THANKS
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.