Hi:

In SP below, at (EXTRACT(YEAR FROM A1.DATE_UP) = :CLOSE_YEAR),
in SQL before making SP, I typically had:

sprintf(CountSQL, "(EXTRACT(YEAR FROM Advertisers.\"DATE_UP\") =
EXTRACT(YEAR FROM CAST(%s AS DATE))))))) " ,
CloseDate.c_str());
IBCountQuerySum1->SQL->Add(CountSQL);

And I wanted to use

EXTRACT(YEAR FROM A1.DATE_UP) = EXTRACT(YEAR FROM :CLOSE_DATE)
but that was not welcome in the SP

I would rather not have to pass the CLOSE_YEAR to SP. No big
deal, I would just rather this worked EXTRACT(YEAR FROM :CLOSE_DATE)

Any suggestions appreciated.

Best regards

SET TERM ## ;
CREATE PROCEDURE ADVERSUMMARIES(CLOSE_DATE DATE, CLOSE_YEAR INT)
RETURNS (ActiveAdver INT, TotalDue DOUBLE PRECISION, CancelledAdver
INT,
CancelledDue DOUBLE
PRECISION,
ActivePaid INT, AmtPaid
DOUBLE PRECISION)
AS
BEGIN
SELECT COUNT (A1.ID), SUM(A2.COST_AD)
FROM
ADVERS A1 INNER JOIN ADVERPAY A2 ON A1.ID = A2.ID
WHERE (((A1.STATUS_REC = 'ACTIVE' AND
A2.EXPIR_DATE > :CLOSE_DATE AND A2.START_DATE <=
:CLOSE_DATE) OR
((A1.STATUS_REC = 'CANCELLED' AND A1.DATE_UP <=
:CLOSE_DATE AND
A2.EXPIR_DATE > :CLOSE_DATE AND
(EXTRACT(YEAR FROM A1.DATE_UP) = :CLOSE_YEAR)))))
INTO ActiveAdver, TotalDue;
SELECT COUNT (A1.ID), SUM(A2.COST_AD)
FROM
ADVERS A1 INNER JOIN ADVERPAY A2 ON A1.ID = A2.ID
WHERE (((A1.STATUS_REC = 'CANCELLED' AND
A1.DATE_UP < :CLOSE_DATE AND
(EXTRACT(YEAR FROM A2.EXPIR_DATE) = :CLOSE_YEAR) AND
(A2.DATE_PAID IS NULL AND
EXTRACT(YEAR FROM A1.DATE_UP) = :CLOSE_YEAR))))
INTO CancelledAdver, CancelledDue;
SELECT COUNT (A1.ID), SUM(A2.COST_AD)
FROM
ADVERS A1 INNER JOIN ADVERPAY A2 ON A1.ID = A2.ID
WHERE (((A1.STATUS_REC = 'ACTIVE' AND
A2.EXPIR_DATE > :CLOSE_DATE AND A2.START_DATE <=
:CLOSE_DATE) OR
((A1.STATUS_REC = 'CANCELLED' AND A1.DATE_UP <=
:CLOSE_DATE AND
A2.EXPIR_DATE > :CLOSE_DATE AND
(EXTRACT(YEAR FROM A1.DATE_UP) = :CLOSE_YEAR)))) AND
A2.DATE_PAID IS NOT NULL)
INTO ActivePaid, AmtPaid;
SUSPEND;
END ##

SET TERM ; ##