Re: PL/SQL returning abnormalities????

And the developer probably is gone, right? (If not he should be fired now).

The central table is here cust with a one to many relation to address and a one to many relation to phonenumbers.
Phonenumbers and addresses are -according to the script- not related. The problem here is the 'primary key' of cust seems to be name_id and this column seems to be null allowed.
An outer join only works when the corresponding record in one of the tables is missing, this means there should be a key in the other record. Also NULL compared with NULL, when not explicitly taken into account is always NULL, and will fall through the cracks with this select. The select in itself is correct, the design and your data is incorrect.

Hth,

Sybrand Bakker, Oracle DBA

seattledba <seattle_at_yahoo.com> wrote in message news:38A30A71.517E0249_at_yahoo.com...
> 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