ORA-01799 [message #353919] |
Wed, 15 October 2008 10:56  |
mhakimjavadi
Messages: 37 Registered: July 2008
|
Member |
|
|
Hi,
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 and I got error ORA-01799. Does anyone have an ideas, just using straight SQL?
I can't use stored procedures or Code.
TableA
ID_Number, Name
TableB
ID_Number, Date_From, Date_To, Address
This is the Query I need (But it's giving me a ORA-01799):
Select TableA.ID_Number,
TableA.Name,
TableB.Address
From TableA, TableB
Where TableB.ID_Number(+) = TableA.ID_Number
TableB.Date_From(+) = (Select max(b.Date_From)
From TableB b
where TableB.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? Thank you in advance.
[EDITED by LF: added [code] tags to preserve formatting]
[Updated on: Wed, 15 October 2008 11:01] by Moderator Report message to a moderator
|
|
|
Re: ORA-01799 [message #353921 is a reply to message #353919] |
Wed, 15 October 2008 11:32   |
subba99
Messages: 31 Registered: September 2008
|
Member |
|
|
hi, you are trying to assign a column to a subquery ,so that u get an error ORA-01799: a column may not be outer-joined to a subquery
remove the outer join for TableB.Date_From then try
like this
Select TableA.ID_Number,
TableA.Name,
TableB.Address
From TableA, TableB
Where TableB.ID_Number(+) = TableA.ID_Number and
TableB.Date_From = (Select max(b.Date_From)
From TableB b
where TableB.ID_Number = b.ID_Number
and b.Date_From <= SYSDATE
and b.Date_to >= SYSDATE)
/
Thanks&Ragards
---------
subbu
|
|
|
|
|
|
Re: ORA-01799 [message #353931 is a reply to message #353929] |
Wed, 15 October 2008 12:24   |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
@mhakimjavadi,
Did you go through the link I posted? You didn't provide a test case for us to work on with. Please do provide a test case with the required output.
Regards,
Jo
|
|
|
|
Re: ORA-01799 [message #353935 is a reply to message #353919] |
Wed, 15 October 2008 13:10   |
mhakimjavadi
Messages: 37 Registered: July 2008
|
Member |
|
|
Hi,
I know the error because of the sub query in join query. However I need to do that. I am looking for any solution that helps me to that that if any.
Thank
|
|
|
|
|
|
|
Re: ORA-01799 [message #353976 is a reply to message #353946] |
Wed, 15 October 2008 23:40  |
 |
Michel Cadot
Messages: 68757 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Yes I am trying to help but if you don't say what you want to achieve how could we say how to do it.
I read your first question but you didn't say anywhere what you want to achieve. You say that you have an error, you want to rewrite the query but you don't say what is the purpose of the query, what is the result you want, what in words is the business need, what result you want the query returns, what is the specification...
Do you understand my question?
Regards
Michel
|
|
|