Robert,

It has been mentioned to you that this is NOT a support list!

Please act accordingly.

-----Original Message-----
From: Robert F. Tulloch [mailto:tultalk@...]
Sent: Wednesday, November 22, 2000 3:19 PM
To: IBDI@egroups.com
Subject: [IBDI] Create Proceedure Question

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 ; ##

Community email addresses:
Post message: IBDI@onelist.com
Subscribe: IBDI-subscribe@onelist.com
Unsubscribe: IBDI-unsubscribe@onelist.com
List owner: IBDI-owner@onelist.com

Shortcut URL to this page:
http://www.onelist.com/community/IBDI