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

> At 02:39 PM 23/08/2004 -0600, you wrote:
>
> >Hi I'm trying to use a stored procedure to collect some accounting
> >information. In my stored procedure I pass in 3 input variables,

an

> >Accounts Receivable Record key value, a Publication key value,

and a date.

> >I use this procedure to basically find out how the ARTransaction

record is

> >split by publication at a certain point in time. In our model an
> >ARTransaction record can be unassigned (null) ie having no

publication key

> >value associated with it. When I use this stored procedure it

works perfect

> >with records that have publication keys (ie not null) as soon as

I use null

> >as a value for the publication key value, I get results I did not

expect.

>
> Passing Nulls in parameters is fine, but your SP code would have

to handle

> nulls in some way. Can't advise anything without seeing the code.
>
> >The weird thing is that I am using IBExpert to create and edit

all my

> >FireBird psql, and when I use the built-in debugger I am able to

walk

> >through my code and view the results that I am expecting.
>
> Debuggers don't execute the SP code, so you won't see run-time

errors

> during debugging.
>
> >It seems that for
> >some reason this stored procedure is not working in isql, or dsql

(which I

> >call from my Delphi application). This is my first time posting

to this

> >news group so I apologize if I have broken any rules.
>
> The main rule is that you try to avoid asking people to search for

a needle

> in a haystack. Present enough details about problems so people

know what

> you need help with.
>
> Your quote:
> Try not to become a man of success but rather to become a man of

value.

> Albert Einstein
>
> 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.

CREATE PROCEDURE SP_AGINGASOF(
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,
BUYER_NO INTEGER,
PUBLICATION_NO INTEGER)
AS
DECLARE VARIABLE LASOF DATE;
DECLARE VARIABLE LCURRENTDAYS NUMERIC(15,2);
DECLARE VARIABLE LTHIRTYDAYS NUMERIC(15,2);
DECLARE VARIABLE LSIXTYDAYS NUMERIC(15,2);
DECLARE VARIABLE LNINETYDAYS NUMERIC(15,2);
DECLARE VARIABLE LNINETYABOVE NUMERIC(15,2);
DECLARE VARIABLE LBUYERNETDUEDAYS INTEGER;
DECLARE VARIABLE LBUYER_NO INTEGER;
DECLARE VARIABLE LPUBLICATION_NO INTEGER;
DECLARE VARIABLE LTOTAL NUMERIC(15,2);
BEGIN
LASOF = ASOF;

/*SELECT ALL PUBLICATION_NO'S ASSOCITATED WITH THIS BUYERS
TRANSACTIONS*/
/*THAT WERE CREATED BEFORE THIS ASOF DATE FOR THIS BUYER */
FOR
SELECT DISTINCT ARTS.BILLTOBUYER_NO
FROM ARTRANSUMMARY ARTS, BUYER B
WHERE B.BUYER_NO = ARTS.BILLTOBUYER_NO
ORDER BY B.BUYERNAME
INTO :LBUYER_NO
DO
BEGIN
FOR
SELECT DISTINCT ART.PUBLICATION_NO
FROM ARTRANSUMMARY ARTS, ARTRAN ART
WHERE ARTS.BILLTOBUYER_NO = :LBUYER_NO
AND ARTS.ARTRANSUMMARY_NO = ART.ARTRANSUMMARY_NO
AND ARTS.DATECREATED <= :LASOF
ORDER BY ART.PUBLICATION_NO
INTO :LPUBLICATION_NO
DO
BEGIN
/*NOW WE HAVE THE RECORD FOR THE BUYER AND THE PUB DO AN
AGING ON IT AND*/
/*RETURN IT USING SUSPEND*/

SELECT CURRENTDAYS, THIRTYDAYS, SIXTYDAYS, NINETYDAYS,
NINETYABOVE, BUYERNETDUEDAYS
FROM SP_AGINGBUYERPUBASOF
(:LBUYER_NO, :LPUBLICATION_NO, :LASOF) SPA

INTO :LCURRENTDAYS, :LTHIRTYDAYS, :LSIXTYDAYS, :LNINETYDAYS, :LNINETY
ABOVE, :LBUYERNETDUEDAYS;

/*THEN TRANSFER THE INPUT/OUTPUT VARS TO LOCAL VARS SO WE
CAN RETURN THE */
/*VALUES THROUGH SUSPEND*/
CURRENTDAYS = LCURRENTDAYS;
THIRTYDAYS = LTHIRTYDAYS;
SIXTYDAYS = LSIXTYDAYS;
NINETYDAYS = LNINETYDAYS;
NINETYABOVE = LNINETYABOVE;
BUYERNETDUEDAYS = LBUYERNETDUEDAYS;
BUYER_NO = LBUYER_NO;
PUBLICATION_NO = LPUBLICATION_NO;

LTOTAL = LCURRENTDAYS + LTHIRTYDAYS + LSIXTYDAYS +
LNINETYDAYS + LNINETYABOVE;

SUSPEND;
END
END
END

This is the inner sp,

CREATE PROCEDURE 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)
AS
DECLARE VARIABLE LCOUNTER INTEGER;
DECLARE VARIABLE LARTRANSUMMARY_NO INTEGER;
DECLARE VARIABLE LFIRSTDATE DATE;
DECLARE VARIABLE LENDDATE DATE;
DECLARE VARIABLE LAGING NUMERIC(15,2);
DECLARE VARIABLE LOUTSTANDING NUMERIC(15,2);
DECLARE VARIABLE LPUBLICATION_NO INTEGER;
DECLARE VARIABLE LASOF DATE;
DECLARE VARIABLE LBUYER_NO INTEGER;
DECLARE VARIABLE LCURRENT NUMERIC(15,2);
DECLARE VARIABLE LTHIRTYDAYS NUMERIC(15,2);
DECLARE VARIABLE LSIXTYDAYS NUMERIC(15,2);
DECLARE VARIABLE LNINETYDAYS NUMERIC(15,2);
DECLARE VARIABLE LNINETYABOVE NUMERIC(15,2);
DECLARE VARIABLE LBUYERNETDUEDAYS INTEGER;
BEGIN
LAGING = 0;
LASOF = ASOF;
LBUYER_NO = BUYER_NO;
LPUBLICATION_NO = PUBLICATION_NO;

SELECT T.NETDUEDAYS
FROM TERMS T, BUYER B
WHERE T.TERMS_NO = B.TERMS_NO
AND B.BUYER_NO = :LBUYER_NO
INTO LBUYERNETDUEDAYS;

/*BUYER NEEDS TO HAVE NETDUEDAYS SET TO BE ABLE TO RUN THIS
PROCEDURE*/
IF(LBUYERNETDUEDAYS IS NULL)THEN
EXCEPTION E_BUYERTERMS;

LCOUNTER = 0;
LFIRSTDATE = ASOF;
LENDDATE = LFIRSTDATE - LBUYERNETDUEDAYS;

WHILE (LCOUNTER <= 4) DO
BEGIN
/*GET ALL ARTRANSUMMARY RECORDS OF TYPE INVOICE THAT ARE
INVOLVED WITH /*
/*THIS PUBLICATION AND THIS BUYER, AND STEP THROUGH THEIR */
IF (LCOUNTER < 4) THEN
BEGIN
FOR
SELECT DISTINCT ARTS.ARTRANSUMMARY_NO
FROM ARTRANSUMMARY ARTS, ARTRAN ART
WHERE ARTS.BILLTOBUYER_NO = :LBUYER_NO
AND ARTS.DATECREATED >= :LENDDATE
AND ARTS.DATECREATED <= :LFIRSTDATE
AND ARTS.ARTRANSUMMARY_NO = ART.ARTRANSUMMARY_NO
AND ART.PUBLICATION_NO = :LPUBLICATION_NO
INTO :LARTRANSUMMARY_NO
DO
BEGIN
SELECT OUTSTANDINGAMOUNT
FROM SP_AGINGPUBOUTSTANDINGASOF
(:LARTRANSUMMARY_NO, :LPUBLICATION_NO, :LASOF)
INTO :LOUTSTANDING;

LAGING = LAGING + LOUTSTANDING;
END
END ELSE
BEGIN
FOR
SELECT DISTINCT ARTS.ARTRANSUMMARY_NO
FROM ARTRANSUMMARY ARTS, ARTRAN ART
WHERE ARTS.BILLTOBUYER_NO = :LBUYER_NO
AND ARTS.DATECREATED <= :LFIRSTDATE
AND ARTS.ARTRANSUMMARY_NO = ART.ARTRANSUMMARY_NO
AND ART.PUBLICATION_NO = :LPUBLICATION_NO
INTO :LARTRANSUMMARY_NO
DO
BEGIN
SELECT OUTSTANDINGAMOUNT
FROM SP_AGINGPUBOUTSTANDINGASOF
(:LARTRANSUMMARY_NO, :LPUBLICATION_NO, :LASOF)
INTO :LOUTSTANDING;

LAGING = LAGING + LOUTSTANDING;
END
END

/*CURRENT - NETDUE*/
IF(LCOUNTER = 0)THEN
BEGIN
LCURRENT = LAGING;
END

/*CURRENT NETDUE TO +30 DAYS*/
IF(LCOUNTER = 1)THEN
BEGIN
LTHIRTYDAYS = LAGING;
END

/*CURRENT NETDUE + 31 DAYS TO 60*/
IF(LCOUNTER = 2)THEN
BEGIN
LSIXTYDAYS = LAGING;
END

/*CURRENT NETDUE + 61 DAYS TO 90*/
IF(LCOUNTER = 3)THEN
BEGIN
LNINETYDAYS = LAGING;
END

/*CURRENT NETDUE + 91 DAYS TO ABOVE*/
IF(LCOUNTER = 4)THEN
BEGIN
LNINETYABOVE = LAGING;
END

LFIRSTDATE = LENDDATE - 1;
LENDDATE = LENDDATE - 29;
LCOUNTER = LCOUNTER + 1;
LAGING = 0;
END

CURRENTDAYS = LCURRENT;
THIRTYDAYS = LTHIRTYDAYS;
SIXTYDAYS = LSIXTYDAYS;
NINETYDAYS = LNINETYDAYS;
NINETYABOVE = LNINETYABOVE;
BUYERNETDUEDAYS = LBUYERNETDUEDAYS;

SUSPEND;
END

Now this sp calls another inner sp, SP_AGINGPUBOUTSTANDINGASOF
which follows

CREATE PROCEDURE SP_AGINGPUBOUTSTANDINGASOF(
ARTRANSUMMARY_NO INTEGER,
PUBLICATION_NO INTEGER,
ASOF DATE)
RETURNS (
OUTSTANDINGAMOUNT NUMERIC(15,2))
AS
DECLARE VARIABLE LARTRANSUMMARY_NO INTEGER;
DECLARE VARIABLE LPUBLICATION_NO INTEGER;
DECLARE VARIABLE LASOF DATE;
DECLARE VARIABLE LZEROREMAININGDATE DATE;
DECLARE VARIABLE LORIGINALAMOUNT NUMERIC(15,2);
DECLARE VARIABLE LAPPLIEDTO NUMERIC(15,2);
DECLARE VARIABLE LOUTSTANDINGAMOUNT NUMERIC(15,2);
BEGIN
LARTRANSUMMARY_NO = ARTRANSUMMARY_NO;
LPUBLICATION_NO = PUBLICATION_NO;
LASOF = ASOF;
LORIGINALAMOUNT = 0;
LAPPLIEDTO = 0;
LOUTSTANDINGAMOUNT = 0;

/*SEE IF THIS ARTRANSSUMMARY RECORD HAS A ZEROREMAINING DATE FIRST
OF ALL*/
SELECT ARTS.ZEROREMAININGDATE
FROM ARTRANSUMMARY ARTS
WHERE ARTS.ARTRANSUMMARY_NO = :LARTRANSUMMARY_NO
INTO :LZEROREMAININGDATE;

/*IF IT DOES THEN WE AUTOMATICALLY RETURN 0 SAVING SOME PROCESSING
POWER*/
IF(LZEROREMAININGDATE IS NOT NULL) THEN
BEGIN
LOUTSTANDINGAMOUNT = 0;
END ELSE
BEGIN
/*OTHERWISE LETS CALCULATE THE CURRENT OUTSTANDING VALUE*/
SELECT TOTAL
FROM SP_AGINGPUBORIGINALASOF
(:LARTRANSUMMARY_NO, :LPUBLICATION_NO)
INTO :LORIGINALAMOUNT;

SELECT TOTAL
FROM SP_AGINGPUBAPPLIEDTOASOF
(:LARTRANSUMMARY_NO, :LPUBLICATION_NO, :LASOF)
INTO :LAPPLIEDTO;

LOUTSTANDINGAMOUNT = LORIGINALAMOUNT - LAPPLIEDTO;
END

OUTSTANDINGAMOUNT = LOUTSTANDINGAMOUNT;
SUSPEND;
END

and this stored proc calls 2 sp's SP_AGINGPUBORIGINALASOF and
SP_AGINGPUBAPPLIEDTOASOF that we use to calculate the remaining
amount left on an accounting record.

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;

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

and the next is

CREATE PROCEDURE SP_AGINGPUBAPPLIEDTOASOF(
ARTRANSUMMARY_NO INTEGER,
PUBLICATION_NO INTEGER,
ASOF DATE)
RETURNS (
TOTAL NUMERIC(15,2))
AS
DECLARE VARIABLE LARTRANSUMMARY_NO INTEGER;
DECLARE VARIABLE LPUBLICATION_NO INTEGER;
DECLARE VARIABLE LASOF DATE;
DECLARE VARIABLE LTOTAL NUMERIC(15,2);
BEGIN
LARTRANSUMMARY_NO = ARTRANSUMMARY_NO;
LPUBLICATION_NO = PUBLICATION_NO;
ASOF = LASOF;

SELECT SUM(ARTSD.AMOUNT)
FROM ARTRANSUMMARYDIST ARTSD
WHERE ARTSD.TOARTRANSUMMARY_NO = :LARTRANSUMMARY_NO
AND ARTSD.PUBLICATION_NO = :LPUBLICATION_NO
AND ARTSD.DATECREATED <= :LASOF
INTO :LTOTAL;

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

TOTAL = LTOTAL;

SUSPEND;
END

Now I have tested these stored procs and they all seem to behave as
I would expect except for the 3rd level one,
SP_AGINGPUBOUTSTANDINGASOF. When I call this one from within the
2nd sp, and I send in a null which one of my accounting records has
as a fk. I expect to see it returned with and outstanding amount.
When I test the 3rd level and 4th level on their own from isql I get
the results I expect. As soon as I try to get this result by
calling the 2nd level sp it craps out on the null value. It doesn't
seem to return the value that is outstanding on the unassinged
publication ar record. This is the exact part that is called,
SELECT OUTSTANDINGAMOUNT
FROM SP_AGINGPUBOUTSTANDINGASOF
(:LARTRANSUMMARY_NO, :LPUBLICATION_NO, :LASOF)
INTO :LOUTSTANDING;
and in this there is an artransummary_no, publication_no is null,
and lasof is a date. Like I said I follow this through the debugger
and it shows what I would expect, until it returns to the 2nd level
sp. At this time it has lost its outstanding amount. It returns
0. I have had problems in the past where I nested sp's a couple
levels deep and I was using the input/output parameters in the
nested sp's input/output parameters and found this caused problems.
I had to resort to creating local variables to hold my i/o parmaters
to send to a nested sp.

I have not really found very much information on the best practices
to code psql in firebird, but more so syntax and structure help
documents so any tips/hints/help are much welcomed. Thanks again
for taking the time to look at this. Todd

PS I originally had all the inner sp's being non-selectable type
sp's meaning (to me) that I just set my output parameters to the
desired values by the time the sp finished, and then in the calling
stored proc accessed these values by using returning_values. I have
just recently changed it to use suspend to see if this would help
get my outstanding value I expect on an unassigned (publication_no
is null) from SP_AGINGPUBOUTSTANDINGASOF.