OraFAQ Forum: SQL & PL/SQL » Multiple Outer Joins

Home » SQL & PL/SQL » SQL & PL/SQL » Multiple Outer Joins

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Multiple Outer Joins [message #197804]

Thu, 12 October 2006 15:09

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.

Report message to a moderator

Re: Multiple Outer Joins [message #197806 is a reply to message #197804]

Thu, 12 October 2006 15:22

joy_division
Messages: 4963
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.

Report message to a moderator

Re: Multiple Outer Joins [message #197809 is a reply to message #197806]

Thu, 12 October 2006 15:42

skooman
Messages: 913
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

Report message to a moderator

Re: Multiple Outer Joins [message #197822 is a reply to message #197809]

Thu, 12 October 2006 16:47

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.

Report message to a moderator