Home » SQL & PL/SQL » SQL & PL/SQL » Oracle 10G Outer join problem. Need help asap
Oracle 10G Outer join problem. Need help asap [message #192283] Mon, 11 September 2006 12:26 Go to next message
shanthi15
Messages: 1
Registered: September 2006
Junior Member
Hello,

I need help. We just migrated to 10G yesterday and now one of my users is unable to fetch data using below in 10.2 (works in 9i). For now I gave an alternate soultion to use variable and plsql but he wants to run as is... ANY HELP please.

select *
from table a, table b
where a.id= b.id (+)
and
(select last_bus_dt from table c)
between a.VALID_FROM and a.valid_to
and
(select prior_bus_dt from table c)
between b.VALID_FROM(+) and b.valid_to(+)

Fails at subquery on table c.
Error is as below:
ora 00936 missing expression
Re: Oracle 10G Outer join problem. Need help asap [message #192428 is a reply to message #192283] Tue, 12 September 2006 06:20 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Without trying it, I'm a bit surprised that it ever worked. B is outer joined to two different tables: A and the sub-query-expression. I thought this was impossible with the old syntax.

Try 9i+ syntax:
select *
from table a
join table c on c.last_bus_dt between a.VALID_FROM and a.valid_to 
cross join table d
left join table b
on a.id = b.id
and d.prior_bus_dt between b.valid_from and b.valid_to


You may not be able to get the old syntax working (althouh it's hard to tell, because you don't give us the "real" query or the precise error message and line number).

If it used to work, it was probably because of a quirk in the optimizer (or perhaps its a different quirk that is causing it to fail now) such as the sub-query expression being converted into a join. My point is, I would discontinue use of your existing query syntax because it staggers dangerously close to being "unsupported".

Ross Leishman
Re: Oracle 10G Outer join problem. Need help asap [message #275362 is a reply to message #192428] Fri, 19 October 2007 11:47 Go to previous messageGo to next message
jrauscher
Messages: 1
Registered: October 2007
Junior Member
I found the same inconsistency between Oracle 10.1.0.2.0 and 10.2.0.1.0.
Re: Oracle 10G Outer join problem. Need help asap [message #275380 is a reply to message #275362] Fri, 19 October 2007 12:58 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Please note this posting is over 1 year old.
Re: Oracle 10G Outer join problem. Need help asap [message #275381 is a reply to message #192283] Fri, 19 October 2007 13:01 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
yes but, at least it shows that this newbie actually used the SEARCH function to find this old but appropriate thread.
Previous Topic: Compare two tables
Next Topic: Rouding issue
Goto Forum:
  


Current Time: Wed Dec 07 22:06:10 CST 2016

Total time taken to generate the page: 0.10726 seconds