Home » SQL & PL/SQL » SQL & PL/SQL » Multiple Outer Joins
Multiple Outer Joins [message #197804] Thu, 12 October 2006 15:09 Go to next message
Nchashim
Messages: 3
Registered: October 2006
Junior Member
I had some down time at work and I am trying to rewrite a query using the new Join syntax.
This works for me:

select a.company_no,
a.account_no LEGACY_ACCTNO,
a.ccs_account_no CCS_ACCTNO,
a.customer_no,
c.last_nm LAST_NAME,
c.first_middle_nm FIRST_NAME,
c.home_phone_area_cd AREA_CD,
c.home_phone_no HOME_PHONE,
ie.phase MTR_PHASE,
i.meter_no_display_form METER,
a.local_office_no,
l.cycle_no,
l.route_no,
a.revenue_class,
a.service_location_no,
l.street_no_whole,
l.street_no_fraction,
l.street_nm,
l.city,
d.distribution_location DLOC,
l.line_type
from t_account a,
t_service_location l,
t_customer c,
t_service_location_dloc d,
T_ITEM i,
t_item_electric ie
where a.company_no = '3'
and a.local_office_no = 50
and a.account_status = 'A'
and a.company_no = l.company_no (+)
and a.account_no = l.account_no (+)
and a.company_no = c.company_no (+)
and a.customer_no = c.customer_no (+)
and l.company_no = d.company (+)
and l.service_location_no = d.service_location_no (+)
AND l.company_no = i.company_no (+)
and l.service_location_no = i.service_location_no (+)
and ie.company_no = i.company_no
and ie.item_no = i.item_no
order by ccs_account_no;

In rewriting, I tried to build up gradually:

select a.company_no,
a.account_no LEGACY_ACCTNO,
a.ccs_account_no CCS_ACCTNO,
a.customer_no CUSTNO,
a.local_office_no,
l.cycle_no,
l.route_no,
a.revenue_class,
a.service_location_no,
l.street_no_whole,
l.street_no_fraction,
l.street_nm,
l.city,
l.line_type
from t_account a LEFT OUTER JOIN t_service_location l
on a.company_no = l.company_no
and a.account_no = l.account_no
where a.company_no = '3'
and a.local_office_no = 50
and a.account_status = 'A'

That worked. But when I tried to add the customer data, I got an error.

select a.company_no,
a.account_no LEGACY_ACCTNO,
a.ccs_account_no CCS_ACCTNO,
a.customer_no CUSTNO,
c.last_nm LAST_NAME,
c.first_middle_nm FIRST_NAME,
c.home_phone_area_cd AREA_CD,
c.home_phone_no HOME_PHONE,
a.local_office_no,
l.cycle_no,
l.route_no,
a.revenue_class,
a.service_location_no,
l.street_no_whole,
l.street_no_fraction,
l.street_nm,
l.city,
l.line_type
from t_account a LEFT OUTER JOIN t_service_location l
on a.company_no = l.company_no
and a.account_no = l.account_no,
t_account aa LEFT OUTER JOIN t_customer c
on aa.company_no = c.company_no
and aa.customer_no = c.customer_no
where a.company_no = '3'
and a.local_office_no = 50
and a.account_status = 'A'

I get an error 01652 - unable to extend temp segment by 640 in tablespace.

Is it not possible to reproduce the old syntax using the new syntax? What am I missing? None of the books or online sources have an example of multiple outer joins.

Re: Multiple Outer Joins [message #197806 is a reply to message #197804] Thu, 12 October 2006 15:22 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I can't help you on the syntax of the newer OUTER JOIN syntax; I'm still old school when it comes to outer joins, but I can tell you that the error you received doesn't have to do with your changing from (+) to the OUTER JOIN syntax.
You just ran out of TEMP space while running the query, which points to you not making the "correct" translation possibly. you are probably getting at least some sort of Cartesian product in your join (maybe leaving one join out).
If it worked with the (+) syntax, it should work with the OUTER JOIN syntax too.
It's only my guess.
Re: Multiple Outer Joins [message #197809 is a reply to message #197806] Thu, 12 October 2006 15:42 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
The from clause should look like:

from t_account a LEFT OUTER JOIN t_service_location l
on a.company_no = l.company_no 
and a.account_no = l.account_no,
LEFT OUTER JOIN t_customer c
on [B]a[/B].company_no = c.company_no
and [B]a[/B].customer_no = c.customer_no


So, you first mention 1 table and than you "add" other ones to it, with a JOIN statement. In you attempt you added t_account to t_account without any join between the two... Hence the temp tablespace Razz

Regards,
Sabine
Re: Multiple Outer Joins [message #197822 is a reply to message #197809] Thu, 12 October 2006 16:47 Go to previous messageGo to next message
Nchashim
Messages: 3
Registered: October 2006
Junior Member
Well, I was already for dances around the office, but

from t_account a LEFT OUTER JOIN t_service_location l
on a.company_no = l.company_no
and a.account_no = l.account_no,
LEFT OUTER JOIN t_customer c
on a.company_no = c.company_no
and a.customer_no = c.customer_no

gives me the message "table or view does not exist". TOAD highlights the second occurance of "LEFT" when this message comes up.
Re: Multiple Outer Joins [message #197828 is a reply to message #197822] Thu, 12 October 2006 18:55 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Get rid of the comma on

and a.account_no = l.account_no,
Re: Multiple Outer Joins [message #197876 is a reply to message #197828] Fri, 13 October 2006 01:30 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Oops, sorry (hey, couldn't test it... Wink). Scott is right.
icon14.gif  Re: Multiple Outer Joins [message #197963 is a reply to message #197828] Fri, 13 October 2006 09:02 Go to previous messageGo to next message
Nchashim
Messages: 3
Registered: October 2006
Junior Member
Thanks. Champagne for all!

(Marian)
Re: Multiple Outer Joins [message #197997 is a reply to message #197963] Fri, 13 October 2006 13:06 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
http://www.orafaq.com/forum/fa/1602/0/
Previous Topic: How to debug in SqlPlus ( .sql file )
Next Topic: Convert output value to 64-Char Hexdecimal
Goto Forum:
  


Current Time: Sat Dec 03 22:14:02 CST 2016

Total time taken to generate the page: 0.10748 seconds