Hi I wonder if anyone can help with a problem I'm having with a stored
procedure.

Basically given a table of pin numbers I look which ones have been
sold or had their status changed between 2 dates (start_date,end_date)
Then for each of these I want to go back through the entire audit
trail to count the number of times they have been either downloaded
onto the system (a debit) or returned back to the central server (a
credit).

The end result is a table that can be used to interface to a reporting
tool.

The output report table is already created and blank before running
the procedure but no data appears in the table at the end.
I did have it working at one point but somehow I seemed to have
managed to stop it working anymore.

Any help/suggestions would be appreciated.

Code below

Regards

Peter

Top Level Procedure



SET TERM ^^ ;
CREATE PROCEDURE P_GEN_PIN_REPORT (
STARTDATE Date,
ENDDATE Date)
AS
/*
Procedure: P_GEN_PIN_REPORT

Author : PJC
Date : 3/3/04
Purpose : To generate pin report that matches invoices
Params : Product, startdate,enddate
------
<param> : <purpose>

*/

DECLARE VARIABLE RESULT_VOUCHER_ID INTEGER;
DECLARE VARIABLE RESULT_PRODUCT_ID INTEGER;
DECLARE VARIABLE RESULT_DOWNLOADS INTEGER;
DECLARE VARIABLE RESULT_UPLOADS INTEGER;
DECLARE VARIABLE RESULT_CREDIT INTEGER;


begin

DELETE FROM PIN_REPORT;

FOR
SELECT DISTINCT VOUCHER_ID,PRODUCT_ID
FROM PINS
WHERE

(PINS.DOWNLOAD_DATE >= :startdate and PINS.DOWNLOAD_DATE
< :enddate + 1)


OR
(PINS.STATUS_CHANGED >= :startdate and PINS.STATUS_CHANGED
< :enddate +1
AND STATUS = 5)

INTO RESULT_VOUCHER_ID,RESULT_PRODUCT_ID

DO
BEGIN
/*
SELECT COUNT(PINS.VOUCHER_ID) FROM PINS WHERE
PINS.VOUCHER_ID = RESULT_VOUCHER_ID INTO RESULT_UPLOADS ;
Here we call the procedure to count the number of
uploads and downloads for
a given voucher id
parameter passed is RESULT_VOUCHER_ID
return is uploads and downloads
*/
execute procedure
P_COUNT_UPLOADS_DOWNLOADS(RESULT_VOUCHER_ID)
returning_values :RESULT_UPLOADS,
:RESULT_DOWNLOADS ;

RESULT_CREDIT = RESULT_DOWNLOADS - RESULT_UPLOADS;
INSERT INTO
PIN_REPORT(VOUCHER_ID,PRODUCT_ID,CREDIT,UPLOADS,DOWNLOADS)
VALUES (
:RESULT_VOUCHER_ID,:RESULT_PRODUCT_ID,:RESULT_CREDIT,:RESULT_UPLOADS,:RESULT_DOWNLOADS);


END
/* NOW WE FILL IN FACE VALUE AND PRODUCT DESCRIPTION */
EXECUTE PROCEDURE P_PIN_REPORT_DETAIL ;
END
^^
SET TERM ; ^^



Count uploads/downloads procedure

SET TERM ^^ ;
CREATE PROCEDURE P_COUNT_UPLOADS_DOWNLOADS (
VOUCHER_ID_PARAM BigInt)
returns (
UPLOADS Integer,
DOWNLOADS Integer)
AS
BEGIN

SELECT COUNT(*) FROM PINS
WHERE PINS.VOUCHER_ID = :VOUCHER_ID_PARAM AND PINS.STATUS = 5
INTO UPLOADS;

SELECT COUNT(*) FROM PINS
WHERE PINS.VOUCHER_ID = :VOUCHER_ID_PARAM INTO DOWNLOADS;
END
^^
SET TERM ; ^^

And finally the report detail procedure that fills in more info about
each pin for the report tool

SET TERM ^^ ;
CREATE PROCEDURE P_PIN_REPORT_DETAIL
AS
/*
You can change this template in the template editor:
File | Preferences | Object Templates

Procedure:

Author : PJC
Date : 21 July 2004
Purpose : to fill in product detials in pin report table
Params
------
<param> : <purpose>

*/

DECLARE VARIABLE PIN_VALUE INTEGER;
DECLARE VARIABLE PROD_ID INTEGER;
DECLARE VARIABLE PROD_DESCRIPTION BLOB(80);

begin

PIN_VALUE = 0;

FOR

SELECT DISTINCT(PIN_REPORT.PRODUCT_ID) , PRODUCT.FACE_VALUE,
PRODUCT.PRODUCT_NAME
FROM PIN_REPORT, PRODUCT
WHERE PIN_REPORT.PRODUCT_ID = PRODUCT.PRODUCT_ID INTO :PROD_ID ,
:PIN_VALUE, PROD_DESCRIPTION

DO

BEGIN

UPDATE PIN_REPORT SET FACE_VALUE = :PIN_VALUE , PRODUCT_NAME =
:PROD_DESCRIPTION WHERE PIN_REPORT.PRODUCT_ID = :PROD_ID;


END


end
^^
SET TERM ; ^^