--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> 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

Thanks heLen, I'm trying very hard so you dont' get frustrated with
me:) I understand fully what you are saying in your previous post,
but I think I am doing exactly what you were stating. I do
reinitialize variables that I need to use again, I don't
reinitialize variables that are selected into since I require their
values to use in the sp. The exact problem I am having is in the
2nd level sp,

SP_AGINGBUYERPUBASOF(
BUYER_NO INTEGER,
PUBLICATION_NO INTEGER,
ASOF DATE)
RETURNS (
CURRENTDAYS NUMERIC(15,2),
THIRTYDAYS NUMERIC(15,2),
SIXTYDAYS NUMERIC(15,2),
NINETYDAYS NUMERIC(15,2),
NINETYABOVE NUMERIC(15,2),
BUYERNETDUEDAYS INTEGER)

this sp calls the 3rd level sp

CREATE PROCEDURE SP_AGINGPUBOUTSTANDINGASOF(
ARTRANSUMMARY_NO INTEGER,
PUBLICATION_NO INTEGER,
ASOF DATE)
RETURNS (
OUTSTANDINGAMOUNT NUMERIC(15,2))

if I call the 3rd level from isql with the following input
parameters, artransummary_no = 4087 (which is fully unassigned for
$10), publication_no = null, and asof is the current date) I get the
reuslt I want. $10 is returned to me through the console. As soon
as I call this from within the second sp I get $0 which is
incorrect. I have followed this through the debugger and when I
step into the 3rd level I see my $10 assigned properly to my output
parameter outstandingamount, when it jumps back to the 2nd level I
see it returned as $10 (remember this is in the debugger). If I
call the 2nd level directly from isql using the following input
parameters SP_AGINGBUYERPUBASOF(11257, null, '8/24/2004')
where buyer_no 11257 owns the record 4087 (and I see this record
selected and passed to the 3rd level sp) this returns $0.

I have been using sp's in firebird for quite a while so I feel very
comfortable with what I am doing, I am wondering that if the 3rd
level sp returns the results I expect when called on its own, why
does it differ when I call it from with in the 2nd level sp. This
isn't the first time I've used nested stored procs, like I mentioned
earlier in this thread I actually had to start mirroring my i/o
variables with local ones so that my nested sp's would work
correctly. Firebird didn't seem to like me using the i/o vars of
one sp as i/o vars in a nested one. Also on this note I try very
hard to intialize my variables before using them or reusing them in
looping constructs. I also did try to test for null (if I'm
understanding your last post correctly) if my input param is null I
do the following

CREATE PROCEDURE SP_AGINGPUBORIGINALASOF(
ARTRANSUMMARY_NO INTEGER,
PUBLICATION_NO INTEGER)
RETURNS (
TOTAL NUMERIC(15,2))
AS
DECLARE VARIABLE LARTRANSUMMARY_NO INTEGER;
DECLARE VARIABLE LPUBLICATION_NO INTEGER;
DECLARE VARIABLE LTOTAL NUMERIC(15,2);
BEGIN
LARTRANSUMMARY_NO = ARTRANSUMMARY_NO;
LPUBLICATION_NO = PUBLICATION_NO;
LTOTAL = 0;

IF (LPUBLICATION_NO IS NULL) THEN
BEGIN
SELECT SUM(DEBIT-CREDIT)
FROM ARTRAN ART
WHERE ART.ARTRANSUMMARY_NO = :LARTRANSUMMARY_NO
AND ART.PUBLICATION_NO IS NULL
INTO :LTOTAL;
END ELSE
BEGIN
SELECT SUM(DEBIT-CREDIT)
FROM ARTRAN ART
WHERE ART.ARTRANSUMMARY_NO = :LARTRANSUMMARY_NO
AND ART.PUBLICATION_NO = :LPUBLICATION_NO
INTO :LTOTAL;
END

IF (LTOTAL IS NULL) THEN
BEGIN
LTOTAL = 0;
END

TOTAL = LTOTAL;

SUSPEND;
END

I set my local mirrored var to the input var then test it for null,
and if it is I tried to use a different select using the is null
conditional instead of = null, although I must say I have never had
issues with that in my other sp's. I really apologize if this is
frustrating I have been able to sail along for the last couple years
using firebird and everything has been A-OK this latest one is
really confusing me though, I'm not sure what I am doing wrong
here. Thanks so much for your help on this heLen. Todd.

Also I did have these nested stored procs not being selectable (I
added this as a ps in my last message:) I have just been grabbing
at straws so I changed them to be selectable (using SUSPEND) sp's to
see if my outcome would differ. Now that I have your word that it
shouldn't matter, I will change them back to executable sp's. Thanks
again. Todd.