Thursday, 19 July 2012

Thursday Thought - Legacy errors

The other day I was writing a query for my Apex application and I required an outer join. In this case I decided to use the ANSI syntax, and out of habit I used a scalar subquery to reference an application item:

select col1, col2
from my_table a
left outer join my_other_table b
on a.id = b.id
and b.sub_id = (SELECT v('F_SUB_ID') FROM DUAL)


I received the following error, which was fair enough - but what I did not expect to see was a reference to what I presume to be Oracle V6.

ORA-01799: a column may not be outer-joined to a subquery

Cause: <expression>(+) <relop> (<subquery>) is not allowed.
Action: Either remove the (+) or make a view out of the subquery. In V6 and before, the (+) was just ignored in this case.
I don't suppose anyone is still on Oracle6 out there?!

1 comment:

Jeffrey Kemp said...

I don't know about the error, looks like a bug from a transformation applied by the CBO - but have you tried changing the outer join to join to a subquery on the table, might work as a workaround.