PL/SQL returning abnormalities????

From: seattledba <seattle_at_yahoo.com>
Date: 2000/02/10
Message-ID: <38A30A71.517E0249@yahoo.com>#1/1

We have a report that gets generated via a PL/SQL Script, and the results are off and I can't determine, from the script what is askew. (written by a developer...left to me, the DBA :-))

The jist of the script is the following:

select cust.format,

cust.type,
cust.description,
cust.notes,
phone.extension,

phone.comments,
phone.type,
addr.format, addr.suite, addr.zip
from customers CUST, PHONENUMBERS PHONE, ADDRESSES ADDR where phone.owner(+) = cust.name_id
and addr.owner(+) = cust.name_id
and cust.popcode = '&1'

What is happening in this script, is that the addr.format is getting lost with the addr.owner(+) = cust.name_id part of the script. The correct addr.format is there for the particular popcode, but gets eliminated when pulling the owner part of the script. However, if you eliminate the addr.owner(+) = contact.name_id part of the script, and just add "and addr.popcode = contact.popcode" too many records are returned.
I don't know why this is the case, as the (+) should still pull the correct addr.format????
What happens, is the addr.format that is returned is what you get the addr.owner is = to the cust.name_id, (which is actually a blank value).

I hope this makes sense.
Is there a way to make this work? Received on Thu Feb 10 2000 - 00:00:00 CST