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

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;

Select allOpen in new window

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;

HI Experts,

i have to use ANSI TYPE JOIN ONLY

i have to use ANSI TYPE JOIN ONLY

The 2 SQL statements from Helena and me ARE ANSI joins! Yours is NOT!

Both examples are ANSI JOINS.

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!

Why ?

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!

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')

Select allOpen 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?

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')

Select allOpen in new window

Been a while since I used the old syntax, but I think that is correct.

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.

THANKS