Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01799
ORA-01799 [message #353919] Wed, 15 October 2008 10:56 Go to next message
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 Go to previous messageGo to next message
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 #353923 is a reply to message #353919] Wed, 15 October 2008 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
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.

Regards
Michel
Re: ORA-01799 [message #353927 is a reply to message #353921] Wed, 15 October 2008 11:55 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member

hmm... The question and the answer provided seems very much familiar. I wonder if it came from this site http://img2.mysmiley.net/imgs/smile/innocent/innocent0002.gif

Regards,
Jo

Re: ORA-01799 [message #353929 is a reply to message #353919] Wed, 15 October 2008 12:19 Go to previous messageGo to next message
mhakimjavadi
Messages: 37
Registered: July 2008
Member
Hi,

I know the error description, I just looking for solution.

Thanks
Re: ORA-01799 [message #353931 is a reply to message #353929] Wed, 15 October 2008 12:24 Go to previous messageGo to next message
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 #353933 is a reply to message #353929] Wed, 15 October 2008 12:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
mhakimjavadi wrote on Wed, 15 October 2008 19:19
Hi,

I know the error description, I just looking for solution.

Thanks


Without knowing what you want to achieve (not what you think you need) anyone can help.

Regards
Michel

Re: ORA-01799 [message #353935 is a reply to message #353919] Wed, 15 October 2008 13:10 Go to previous messageGo to next message
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 #353936 is a reply to message #353935] Wed, 15 October 2008 13:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
However I need to do that.

If you say so.

Quote:
I am looking for any solution that helps me to that that if any.

How could we answer if you don't give the question?
Once again post "what you want to achieve not what you think you need".

Regards
Michel
Re: ORA-01799 [message #353945 is a reply to message #353927] Wed, 15 October 2008 15:09 Go to previous messageGo to next message
mhakimjavadi
Messages: 37
Registered: July 2008
Member
Yes I checked the link but the solution did not work.

Thank a lot
Re: ORA-01799 [message #353946 is a reply to message #353936] Wed, 15 October 2008 15:10 Go to previous messageGo to next message
mhakimjavadi
Messages: 37
Registered: July 2008
Member
I sent a sample query that I need to use. Are you trying to help. Please look at my frist question.

Thanks
Re: ORA-01799 [message #353957 is a reply to message #353919] Wed, 15 October 2008 18:54 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Here is one possible alternative. However, be aware that symantically these are not technically sematically equivelant.

SQL> 
SQL> select *
  2  from dual a
  3  where a.dummy(+) = (select dummy from dual)
  4  /
where a.dummy(+) = (select dummy from dual)
                                          *
ERROR at line 3:
ORA-01799: a column may not be outer-joined to a subquery


SQL> select *
  2  from dual a
  3  where a.dummy = (select dummy from dual)(+)
  4  /
where a.dummy = (select dummy from dual)(+)
                                        *
ERROR at line 3:
ORA-00933: SQL command not properly ended


SQL> 
SQL> 
SQL> with
  2       subquery as (select * from dual)
  3  select *
  4  from dual
  5      ,subquery
  6  where dual.dummy = subquery.dummy(+)
  7  /

D D
- -
X X

1 row selected.

SQL> with
  2       subquery as (select * from dual)
  3  select *
  4  from dual
  5      ,subquery
  6  where dual.dummy(+) = subquery.dummy
  7  /

D D
- -
X X

1 row selected.

SQL> 

Good luck, Kevin
Re: ORA-01799 [message #353976 is a reply to message #353946] Wed, 15 October 2008 23:40 Go to previous message
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
Previous Topic: ORA-00936 when trying to insert
Next Topic: Missing weeks should contain the value prior to the one that is present
Goto Forum:
  


Current Time: Mon Apr 28 14:31:44 CDT 2025