Re: ORA-01719 - experiences please

martin_at_pyrite (Martin Purbrook) wrote:
>
>Dear Everyone,
>
>I'm getting the above error in a SQL query, due to the following construct:
>...
>a.column1 = b.column1 (+) and
>b.column2 (+) in ('A', 'B') and
>...
>This used to work in Version 6, but for some reason is not allowed in O7.
>
>I realise that one solution is to split the query into a union along the
>following lines:
>...
>a.column1 = b.column1 (+) and
>b.column2 (+) = 'A' and
>...
>union
>...
>a.column1 = b.column1 (+) and
>b.column2 (+) = 'B' and
 

>However, this is already a _big_ query (on second thoughts, it's bigger than
>that, it's a _BIG_ query), with two unions, and I'm keen not to add more
>unions if I can solve the problem another way.
>
>If anybody has any experience of this problem, particularly with porting a
>Version 6 query, could they share the solution please?
>
>Does anybody know why this change has taken place in Oracle7, as it doesn't
>appear in Appendix A "Summary of changes to SQL in Oracle7" in the "Oracle7
>Server SQL Language Reference Manual"?
>
>Regards,
 

>
>MPP
Martin,

I would suggest removing the outer join syntax from the literal comparison and using NVL.

e.g. NVL(b.column2,'Whatever') in ('A','B')

I don't think outer joins against literals makes logical sense. Correct me if I'm wrong but the predicate will always be true since (+) means to provide a null row even it there's not a match.

Ken F.

-- 































~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~