Re: SQL spooling two tables to one spoolfile ??

Oracle SQL does limit outer joins to single sided.

 As a work around, you can do two (or three) separate single sided outer joins and union the results.

Select ...
from tab1, tab2
where tab1.col1 (+) = tab2.col1
union
select ...
from tab1, tab2
where tab2.col1 (+) = tab1.col1

Also,
for 3 tables, outer joins can be chained... Select ...
from tab1, tab2, tab3
where tab1.col1 = tab2.col1 (+)
and tab2.col1 = tab3.col1(+)

Also
more than one table can be outer joined to a single 'base' table Select ...
from tab1, tab2, tab3
where tab1.col1 = tab2.col1 (+)
and tab1.col1 = tab3.col1(+)

but NOT of course..

Select ...        /* invalid outer join example*/
from     tab1, tab2, tab3
where  tab1.col1(+) = tab2.col1
and      tab1.col1 (+) = tab3.col1(+)

HTH mark

Anders Nilsson wrote in message ...
>Hi Claire!
>
>Thank's for a promtp answer, but...
>
>...unfortunately I forgot to explain that all phonenumbers can be missing.
>
>That means that I should add (+) (i.e an outer join) on tmp_phone1,
tmp_phone2 and tmp_phone3.
>
>As far as I know Oracle don't allow me to have outer joins on more than one
table (or alias),
> so I guess it's back to the drawingtable again....
>
>Regards
>
>Anders
>
>
>
>
>
>In article <01be4157$1d4b8000$0d3a0dd8_at_clairema>, cmarcoux_at_webnet.qc.ca
says...
>>
>>Hi! Anders
>>Just add (+) in your WHERE clause after tmp_phone3.customer_id(+) AND
>>after tmp_phone3.usage_id (+) = 3
>>Good Luck
>>Claire M.
>>
>>
>>Anders Nilsson <anders.nilsson_at_mbox10.swipnet.se> a écrit dans l'article
>><hmZn2.30370$9S3.76941_at_nntpserver.swip.net>...
>>> I have two tables with following columns....
>>>
>>> TABLE CUSTOMER
>>> customer_id
>>> first_name
>>> sur_name
>>>
>>> TABLE TELEPHONE_NO
>>> telephone_nr_id
>>> customer_id
>>> usage_id (where 1=homephone, 2=workphone, 3=mobile phone and so on)
>>> area_code
>>> phone_no
>>>
>>> that I want to spool to a file in this format:
>>>
>>> JOINED TABLES
>>> cust_no first_name sur_name area_1 phone_1 area_2 phone_2
>>area_3 phone_3
>>> -------- ---------- --------- ------- -------- ------- --------
>>------- -------
>>> 0001 Anders Nilsson 123 823421 123 823422 123
>> 823423
>>> 0002 Sven Svensson 134 123456 123 123457
>>>
>>> and I can do this with the following script:
>>>
>>> spool c:\temp.txt
>>> SELECT customer.customer_id cust_no,
>>> customer.first_name,
>>> customer.sur_name,
>>> tmp_phone1.area_code area_1,
>>> tmp_phone1.phone_no phone_1,
>>> tmp_phone2.area_code area_2,
>>> tmp_phone2.phone_no phone_2,
>>> tmp_phone3.area_code area_3,
>>> tmp_phone3.phone_no phone_3
>>> FROM customer,
>>> telephone_no tmp_phone1,
>>> telephone_no tmp_phone2,
>>> telephone_no tmp_phone3
>>> WHERE customer.customer_id = tmp_phone1.customer_id AND
>>> customer.customer_id = tmp_phone2.customer_id AND
>>> customer.customer_id = tmp_phone3.customer_id AND
>>> tmp_phone1.usage_id = 1 AND
>>> tmp_phone2.usage_id = 2 AND
>>> tmp_phone3.usage_id = 3
>>> ;
>>> spool off
>>>
>>> The problem is that customer 0002 wont appear in the spoolfile since he
>>doesn't have the third
>>> telephonenumber.
>>>
>>> How do I solve this problem with PL/SQL?
>>>
>>> Thank's in advance!
>>>
>>> Anders
>>>
>>> PS:
>>>
>>> Some example data:
>>>
>>> EXAMPLE CUSTOMER
>>> customer_id first_name sur_name address
>>> 0001 Anders Nilsson Testgatan 1
>>> 0002 Sven Svensson Testgatan 18
>>>
>>> EXAMPLE TELEPHONE_NO
>>> telephone_nr_id customer_id usage_id area_code phone_no
>>> 100001 001 1 123 823421
>>> 100002 001 2 123 823422
>>> 100003 001 3 123 823423
>>> 100004 002 1 134 123456
>>> 100005 002 2 123 123457
>>>
>>>
>>>
>>>
>
Received on Sat Jan 16 1999 - 20:12:06 CST