At 04:04 AM 24/08/2004 +0000, you wrote:

> >
> > Support list paraphrase:
> > Try not to present a problem description of "guess" but a rather
>problem
> > description of value.
> >
> > ./heLen
>
>Thanks heLen, remember I'm new at this and I don't want to overload
>everyone with useless information so I was just trying to give
>enough info but not overload. This is what I am doing and hopefully
>this will help. This is the main sp I call which in turn calls an
>inner sp.

OK, slight "overload" here, so let's establish a couple of principles about
SPs that might help you to overcome the problems you're getting from
NULLs. The problems you're having with nulls is closely-related to the
currently running thread that Lucas Franzen has been dealing with for Muthu
Annamalai...."Coalesce Help in Stored Procedures".

Variables are just variables. If you don't initialise them, they keep
whatever value they last had.
So, when you SELECT...INTO <vars> inside a FOR... loop, the vars will get
new values (or null, if there's a null in the fetched column) each time a
row is found.

Now, the problem you are having is with the non-looping SELECT ... INTO
<vars>. If there is no set returned, the variables just keep whatever
value they had previously. You are putting your proc in double jeopardy
because (a) you're not testing inputs to see whether they are null and (b)
you're not re-initialising variables before you re-use them.

You also seem to be assuming that you will get a set back by matching
NULL=NULL. You don't. You get nothing. You must test your search values
for null and, if you want to fetch a row where aValue = :someValue, and the
var someValue happens to be null, you must make the search criterion
"..where...or aValue is null..."

The other problem I see here is invoking selectable SPs from within the
procedure where you are expecting a singleton result. Don't. Write these
as executable procedures (no SUSPEND calls in them) and invoke them using a
RETURNING_VALUES(:VAR1, :VAR2, ....). These calls will return whatever the
called procedure outputs from its RETURNS clause.

Another point to make is about your use of implicit inner joins. It's time
to get away from these (they have been deprecated by SQL standards since
1992) and use explicit join syntax for all joins, including inner ones, e.g.

express this:

SELECT DISTINCT ARTS.BILLTOBUYER_NO
FROM ARTRANSUMMARY ARTS, BUYER B
WHERE B.BUYER_NO = ARTS.BILLTOBUYER_NO

like this:

SELECT DISTINCT ARTS.BILLTOBUYER_NO
FROM ARTRANSUMMARY ARTS
join BUYER B
on B.BUYER_NO = ARTS.BILLTOBUYER_NO

It makes both dsql and psql easier to debug and, in fact, some component
interfaces get confused about WHERE clauses that contain join criteria.

./heLen