Unanswered: ORA-01799 Outer Joining a column to a subquery
I bet this one will stump the GURUs. Good luck with this puzzle.
I need to do an outer join using a column where if the key is found, I need to select the max of that column. Oracle won't let me do an outer join to a subquery. Does anyone have an ideas, just using straight SQL? I can't use stored procedures or Code.
ID_Number, Date_From, Date_To, Address
This is the Query I need (But it's giving me a ORA-01799):
Select Table1.ID_Number, Table1.Name, Table2.Address
From Table1, Table2
Where Table1.ID_Number = Table2.ID_Number(+)
Table2.Date_From(+) = (Select max(b.Date_From) From Table2 b where Table2.ID_Number = b.ID_Number and b.Date_From <= SYSDATE and b.Date_to >= SYSDATE)
How do I return the Table1 rows even if there are no rows in Table2?
If I put Table2.Date_From = (Select max(b.Date_From)......... then if there are no rows in Table2 for that ID_Number, the query returns nothing, but I need it to return something.
One more caviat, I can't do the select(max) query in the "FROM" clause. I tried it and the query would take hours to run with the millions of rows in my table.
join Table2 ZZ
on Table1.ID_Number = ZZ.ID_Number
where ZZ.Date_From =
( Select max(Date_From)
where Table2.ID_Number = ZZ.ID_Number
and Date_From <= SYSDATE
and Date_to >= SYSDATE