I have a stored procedure that is failing. The current query behind it is:
SELECT DISTINCT
<many, many values>
FROM
table1
LEFT OUTER JOIN table2 ON
(table2.key =
(select max (table2.key) from table2
where table2.key = table1.key) or
table2.key is null)
INNER JOIN table3 ON
table1.key = table3.key
INNER JOIN view1 ON
table3.key = view1.key
INNER JOIN table4 ON
view1.key = table4.key
INNER JOIN table5 groupdesc ON
table5.key = groupdesc.key
INNER JOIN table6 ON
table3.key = table6.key
INNER JOIN table5 methoddesc ON
table6.key = methoddesc.key
INNER JOIN table7 ON
view1.key = table7.key
INNER JOIN table8 ON
table8.key = table7.key
INNER JOIN table5 iddesc ON
table8.key = iddesc.key
LEFT OUTER JOIN view2 ON
view2.key = view1.key
INNER JOIN table9 ON
(table9.key = table1.key)
INNER JOIN table10 ON
(table10.key = view1.key)
WHERE
<many, many conditions>;
This fails because of the left outer join on a subquery. I have tried to get around this in several ways, without much success. One that I was certain should work is to replace the subquery with an inline view, like so:
SELECT DISTINCT
<many, many values>
FROM
(select MAX(table2.key) AS key from table2,table1
where table2.key = table1.key) VW,
table1
LEFT OUTER JOIN table2 ON
(table2.key = VW.key or table2.key is null)
INNER JOIN table3 ON
table1.key = table3.key
INNER JOIN view1 ON
table3.key = view1.key
INNER JOIN table4 ON
view1.key = table4.key
INNER JOIN table5 groupdesc ON
table5.key = groupdesc.key
INNER JOIN table6 ON
table3.key = table6.key
INNER JOIN table5 methoddesc ON
table6.key = methoddesc.key
INNER JOIN table7 ON
view1.key = table7.key
INNER JOIN table8 ON
table8.key = table7.key
INNER JOIN table5 iddesc ON
table8.key = iddesc.key
LEFT OUTER JOIN view2 ON
view2.key = view1.key
INNER JOIN table9 ON
(table9.key = table1.key)
INNER JOIN table10 ON
(table10.key = view1.key)
WHERE
<many, many conditions>;
This, however, results in a ORA-00904 (VW.KEY an invalid identifier) on the red line. Am I going about this the right way? If so, what am I doing wrong?