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?!
Post a Comment