Re: SQL spooling two tables to one spoolfile ??

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 - 17:40:03 CST