troubleshooting Question

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

Avatar of rdefuria
rdefuriaFlag for United States of America asked on
Oracle Database
4 Comments1 Solution5637 ViewsLast Modified:
Hello,

I need help rewriting the following Oracle query.
Specifically, the second line in the where clause
generates the following ORA-01719 error:
"outer join operator (+) not allowed in operand of OR or IN"

How can this be rewritten (e.g., using a subquery,
a union, etc.) to preserve the meaning of the query.


SELECT
   'PlaceHolder', A."Name", A."Dept",
   MAX("SomeValue") as "SomeValue",
   MAX(B."AnotherValue") as "AnotherValue"
FROM
   "ViewA" A, "ViewB" B
WHERE
   (A."Name" = B."Name"(+)) AND
   ((A."Dept" = B."Dept"(+)) OR (A.Dept IS NULL AND B.Dept IS NULL)) AND
   (B."CompName" = 'abc123\' OR B."CompName" IS NULL) AND
   (A."CompName" LIKE 'abc123\%')
GROUP BY
   A."Name", A."Dept"
ORDER BY
   A."Name"
;


TIA.
ASKER CERTIFIED SOLUTION
Mark Geerlings
Database Administrator, retired

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros