Re: PL/SQL returning abnormalities????

Sybrand;
You are correct in some of your observations. I believe what is attempting to be done, is additional relationship building between various tables, through fields within these tables (outside of the primary key). That's fine and dandy,
There are NULL values allowed within the two fields trying to establish relationships in this script using the outer join -- (name_id and owner).

What appears to be the main goal with this particular script, is to pull "Siteinfo" for a particular popcode. This means identifying the popcode, and then in turn identify the address and phonenumber associated with that popcode.
What should happen with this script, is that the Popcode should be identical between all three tables, and should pull the address information from the addresses table, the phonenumber information from the phonenumbers table, and the contact information from the contacts table, and place it all together in a nice little text file. The problem comes from trying to eliminate records and establish a "relationship" with the owner and and name_id fields. Plus, in one or more of the tables, the actual Popcode may be null.

This "relationship" can't be taken out of the script, because at times there may be two popcodes with different overall values in the name_id and owner fields.

It's hard to explain what I have found without posting a bunch of table definitions and scripts I've written to pull various information tho....but the relationship surely isn't working!

Seems like there could be a mess here.

Sybrand Bakker wrote:

> 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