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 - 07:46:15 CST
