Re: SQL spooling two tables to one spoolfile ??

Hi,

Sorry if I'm a bit off topic but I thought you'd be interested...

Have you ever considered doing these kinds of things via Excel ? You could fetch your data into Excel and then reformat the data there.

I wrote Oraxcel, a utility that gives Excel Oracle access. Please have a look at it. You can find more info at

http://members.aol.com/gjlinker

Regards, Gerrit-Jan Linker
Developer of Oraxcel, OraWeb, OraCodes and OraSQL http://members.aol.com/gjlinker

In article <hmZn2.30370$9S3.76941_at_nntpserver.swip.net>, anders.nilsson_at_mbox10.swipnet.se (Anders Nilsson) writes:

>Subject: SQL spooling two tables to one spoolfile ??
>From: anders.nilsson_at_mbox10.swipnet.se (Anders Nilsson)
>Date: Sat, 16 Jan 1999 09:55:25 GMT
>
>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
>
>
>
>
>------------------- Headers --------------------
>
>Path:
>lobby01.news.aol.com!newstf02.news.aol.com!portc01.blue.aol.com!news-peer
.gip.net!news-peer1.sprintlink.net!news-peer-europe.sprintlink.net!news.sp rintlink.net!Sprint!newsfeed1.swip.net!swipnet!nntpserver.swip.net!not-for-mail
>Newsgroups: comp.databases.oracle.misc
>Subject: SQL spooling two tables to one spoolfile ??
>From: anders.nilsson_at_mbox10.swipnet.se (Anders Nilsson)
>Organization: ..
>X-Newsreader: WinVN 0.99.9 (Released Version) (x86 32bit)
>MIME-Version: 1.0
>Content-Type: Text/Plain; charset=US-ASCII
>Lines: 75
>Message-ID: <hmZn2.30370$9S3.76941_at_nntpserver.swip.net>
>Date: Sat, 16 Jan 1999 09:55:25 GMT
>NNTP-Posting-Host: 130.244.200.216
>X-Complaints-To: news-abuse_at_swip.net
>X-Trace: nntpserver.swip.net 916480525 130.244.200.216 (Sat, 16 Jan 1999
>10:55:25 MET DST)
Received on Thu Jan 21 1999 - 16:05:21 CST