Hi:

IBConsole: Preparing below return SQL Parse Error Parameter
Name Expected. I have gone through this over and over to make sure
names are correct and in correct sequence. Can't see problem so I
must conclude that 1. I am blind; 2. I don't know what I am doing;
3. One cannot do this.

Any help appreciated.

Best regards


SET TERM ## ;
CREATE PROCEDURE SUMMARY2COUNT(START_DATE DATE, CLOSE_DATE DATE,
REPORT_YEAR INT)
RETURNS (ID INT, COUNTRENEWDUE INT, SUMRENEWDUE DOUBLE PRECISION,
COUNTRENEWPAID INT, SUMRENEWPAID DOUBLE PRECISION, TOTNEW INT,
NEWFEES DOUBLE PRECISION, NEWDUES DOUBLE PRECISION, TRGPAID DOUBLE
PRECISION, NEWMEMTRGPAID DOUBLE PRECISION, RSTATFEES DOUBLE
PRECISION, COUNTCREDITDUE INT, COUNTCREDITPAID INT, SUMCREDITDUE
DOUBLE PRECISION, SUMCREDITPAID DOUBLE PRECISION)
AS
BEGIN
SELECT ID,
(SELECT COUNT(S2.ID)
FROM Sum2 S2
WHERE ((S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE' )
OR
(S2.STATUSMEM = 'CANCELLED' AND S2.MEMCANCEL > :Close_Date)) AND
S2.PAYYEAR = :Report_Year AND S2.RENEWDATE >= :Start_Date AND
S2.RENEWDATE <= :Close_Date) ,
(SELECT SUM (S2.DUESAMT)
FROM Sum2 S2
WHERE ((S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE' )
OR
(S2.STATUSMEM = 'CANCELLED' AND S2.MEMCANCEL > :Close_Date)) AND
S2.PAYYEAR = :Report_Year AND S2.RENEWDATE >= :Start_Date AND
S2.RENEWDATE <= :Close_Date) ,
(SELECT COUNT(S2.ID)
FROM Sum2 S2
WHERE ((S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE' )
OR
(S2.STATUSMEM = 'CANCELLED' AND S2.MEMCANCEL > :Close_Date)) AND
S2.DUESPAID IS NOT NULL AND
S2.PAYYEAR = :Report_Year AND S2.RENEWDATE >= :Start_Date AND
S2.RENEWDATE <= :Close_Date) ,
(SELECT SUM (S2.DUESAMT)
FROM Sum2 S2
WHERE ((S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE' )
OR
(S2.STATUSMEM = 'CANCELLED' AND S2.MEMCANCEL > :Close_Date)) AND
S2.DUESPAID IS NOT NULL AND
S2.PAYYEAR = :Report_Year AND S2.RENEWDATE >= :Start_Date AND
S2.RENEWDATE <= :Close_Date) ,
(SELECT COUNT (DISTINCT S2.ID)
FROM Sum2 S2
WHERE (S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE'
OR S2.STATUSMEM = 'CANCELLED' ) AND
S2.INITPAID >= :Start_Date AND S2.INITPAID <=
:Close_Date ) ,
(SELECT SUM (S2.INITFEE)
FROM Sum2 S2
WHERE (S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE'
OR S2.STATUSMEM = 'CANCELLED' ) AND
S2.INITPAID >= :Start_Date AND S2.INITPAID <=
:Close_Date ),
(SELECT SUM (S2.INITDUES)
FROM Sum2 S2
WHERE (S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE'
OR S2.STATUSMEM = 'CANCELLED' ) AND
S2.INITPAID >= :Start_Date AND S2.INITPAID <=
:Close_Date ),
(SELECT SUM (S2.TRGDUES)
FROM Sum2 S2
WHERE ((S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE' )
AND
S2.RENEWDATE >= :Start_Date AND S2.RENEWDATE <= :Close_Date AND
S2.DUESPAID <= :Close_Date ) OR
(S2.STATUSMEM = 'CANCELLED' AND S2.MEMCANCEL > :Close_Date AND
S2.RENEWDATE >= :Start_Date AND S2.RENEWDATE <= :Close_Date )),
(SELECT SUM (S2.TRGDUES)
FROM Sum2 S2
WHERE ((S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE'
OR S2.STATUSMEM = 'CANCELLED') AND
S2.DUESPAID IS NULL AND S2.PAYYEAR = :Report_Year)),
(SELECT SUM(S2.RESTATAMT)
FROM Sum2 S2
WHERE (S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE' )
AND
S2.DUESPAID >= :Start_Date AND S2.DUESPAID <=
:Close_Date),
(SELECT COUNT (S2.ID)
FROM Sum2 S2
WHERE ((S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE'
OR
STATUSMEM = 'CANCELLED') AND
S2.CREDITDUE >= :Start_Date AND S2.CREDITDUE <=
:Close_Date AND S2.PAYYEAR = :Report_Year)),
(SELECT COUNT (S2.CREDITPAID)
FROM Sum2 S2
WHERE ((S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE'
OR
STATUSMEM = 'CANCELLED') AND
S2.CREDITDUE >= :Start_Date AND S2.CREDITDUE <= :Close_Date) AND
S2.CREDITPAID <= :Close_Date AND S2.PAYYEAR = :Report_Year),
(SELECT SUM (S2.CREDITAMT)
FROM Sum2 S2
WHERE ((S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE'
OR
STATUSMEM = 'CANCELLED') AND
S2.CREDITDUE >= :Start_Date AND S2.CREDITDUE <=
:Close_Date AND S2.PAYYEAR = :Report_Year )) ,
(SELECT SUM (S2.CREDITAMT)
FROM Sum2 S2
WHERE ((S2.STATUSMEM = 'ACTIVE' OR S2.STATUSMEM = 'ASSOCIATE'
OR
STATUSMEM = 'CANCELLED') AND
S2.CREDITDUE >= :Start_Date AND S2.CREDITDUE <= :Close_Date) AND
S2.CREDITPAID <= :Close_Date AND S2.PAYYEAR <= :Report_Year)
FROM Sum2 S22
WHERE S22.ID = 0

INTO :Id, :CountRenewDue, :SumRenewDue, :CountRenewPaid,
:SumRenewPaid, :TotNew, :NewFees, : NewDues, :TRGPaid,
:NewMemTRGPaid, :RStatFees, :CountCreditDue, :CountCreditPaid,
:SumCreditDue, :SumCreditPaid;


EXIT;
END ##

SET TERM ; ##