| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL spooling two tables to one spoolfile ??
On Sat, 16 Jan 1999 09:55:25 GMT, anders.nilsson_at_mbox10.swipnet.se (Anders
Nilsson) wrote:
>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?
With straight SQL you can use an outer join to handle it.
Using your example data...
SQL> select * from customer;
CUSTOMER_ID FIRST_NAME SUR_NAME ADDRESS
----------- ---------- -------- ------------------------------
1 Anders Nilsson Testgatan 1
2 Sven Svensson Testgatan 18
SQL> select * from telephone_no;
TELEPHONE_NR_ID CUSTOMER_ID USAGE_ID AREA_CODE PHONE_NUMBER --------------- ----------- ---------- ---------- ------------
1 1 1 123 823421
2 1 2 123 823422
3 1 3 123 823423
4 2 1 134 123456
5 2 2 123 123457
1 SELECT customer.customer_id cust_no,
2 customer.first_name,
3 customer.sur_name,
4 tmp_phone1.area_code area_1,
5 tmp_phone1.phone_number phone_1,
6 tmp_phone2.area_code area_2,
7 tmp_phone2.phone_number phone_2,
8 tmp_phone3.area_code area_3,
9 tmp_phone3.phone_number phone_3
10 FROM customer,
11 telephone_no tmp_phone1,
12 telephone_no tmp_phone2,
13 telephone_no tmp_phone3
14 WHERE customer.customer_id = tmp_phone1.customer_id(+) AND
15 customer.customer_id = tmp_phone2.customer_id(+) AND
16 customer.customer_id = tmp_phone3.customer_id(+) AND
17 tmp_phone1.usage_id(+) = 1 AND
18 tmp_phone2.usage_id(+) = 2 AND
19* tmp_phone3.usage_id(+) = 3
0001 Anders Nilsson 123 823421 123 823422 123 823423 0002 Sven Svensson 134 123456 123 123457
Hope this helps.
chris.
>
>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
>
>
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |