Greetings All,

Using Firebird 1.5.3

D.CREATE_DATE is defined as a TimeStamp in the database.

V_END_DATE is an input parameter defined as Date
V_END_DATETIME is an input parameter defined as TimeStamp

I have a stored procedure with the following execute statment

EXECUTE STATEMENT 'SELECT COALESCE(COUNT(D.DEBT_NO),0) AS TOTAL_COUNT,
COALESCE(SUM(D.ORIG_PRINCIPAL +
D.ADJ_PRINCIPAL),0) AS TOTAL_GROSS,
(SUM(D.LIST_DATE -
D.SERVICE_DATE)/COALESCE(COUNT(D.DEBT_NO),1)) AS AVG_AGE
FROM DEBT D
JOIN ACCT_CASE AC ON AC.ACCT_ID = D.ACCT_ID AND
AC.CASE_ID = D.CASE_ID
WHERE AC.CLT_ID = ' || V_CLT_ID ||
'AND NOT AC.CLT_SITE_ID IN (' ||
V_CLT_SITE_ID_LIST || ')
AND EXTRACT(MONTH FROM D.LIST_DATE) = ' ||
iBegMonth ||
'AND EXTRACT(YEAR FROM D.LIST_DATE) = ' ||
iBegYear ||
'AND CAST(D.CREATE_DATE AS DATE) <= ' ||
V_END_DATE
INTO :R_TOTAL_COUNT, :R_TOTAL_GROSS_AMT,
:R_AVG_AGE;

When I run the stored procedure I receive the follow error:

ISC ERROR CODE: 335544606
ISC ERROR MESSAGE:
expression evaluation not supported

I tracked it down to the CAST(D.CREATE_DATE AS DATE) line.
I guess I have never tried to use a CAST() inside of a execute statement
before. Is this correct?

So, as a work around I thought I would modify it as follows:

EXECUTE STATEMENT 'SELECT COALESCE(COUNT(D.DEBT_NO),0) AS TOTAL_COUNT,
COALESCE(SUM(D.ORIG_PRINCIPAL +
D.ADJ_PRINCIPAL),0) AS TOTAL_GROSS,
(SUM(D.LIST_DATE -
D.SERVICE_DATE)/COALESCE(COUNT(D.DEBT_NO),1)) AS AVG_AGE
FROM DEBT D
JOIN ACCT_CASE AC ON AC.ACCT_ID = D.ACCT_ID AND
AC.CASE_ID = D.CASE_ID
WHERE AC.CLT_ID = ' || V_CLT_ID ||
'AND NOT AC.CLT_SITE_ID IN (' ||
V_CLT_SITE_ID_LIST || ')
AND EXTRACT(MONTH FROM D.LIST_DATE) = ' ||
iBegMonth ||
'AND EXTRACT(YEAR FROM D.LIST_DATE) = ' ||
iBegYear ||
'AND D.CREATE_DATE <= ' || V_END_DATE || '
23:59:59'
INTO :R_TOTAL_COUNT, :R_TOTAL_GROSS_AMT,
:R_AVG_AGE;

ISC ERROR CODE: 335544569
ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 7, char 141
23

So then I thought I would try this:

DECLARE VARIABLE tsEndDate TimeStamp;
begin
tsEndDate = :V_END_DATE || ' 23:59:59';


EXECUTE STATEMENT 'SELECT COALESCE(COUNT(D.DEBT_NO),0) AS TOTAL_COUNT,
COALESCE(SUM(D.ORIG_PRINCIPAL +
D.ADJ_PRINCIPAL),0) AS TOTAL_GROSS,
(SUM(D.LIST_DATE -
D.SERVICE_DATE)/COALESCE(COUNT(D.DEBT_NO),1)) AS AVG_AGE
FROM DEBT D
JOIN ACCT_CASE AC ON AC.ACCT_ID = D.ACCT_ID AND
AC.CASE_ID = D.CASE_ID
WHERE AC.CLT_ID = ' || V_CLT_ID ||
'AND NOT AC.CLT_SITE_ID IN (' ||
V_CLT_SITE_ID_LIST || ')
AND EXTRACT(MONTH FROM D.LIST_DATE) = ' ||
iBegMonth ||
'AND EXTRACT(YEAR FROM D.LIST_DATE) = ' ||
iBegYear ||
'AND D.CREATE_DATE <= ' || tsEndDate
INTO :R_TOTAL_COUNT, :R_TOTAL_GROSS_AMT,
:R_AVG_AGE;

Same Error:

ISC ERROR CODE: 335544569
ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 7, char 141
23

Then I thought I would just try something simple.

EXECUTE STATEMENT 'SELECT FIRST 1 D.CREATE_DATE
FROM DEBT D
WHERE D.CREATE_DATE <= ' || :V_END_DATETIME
INTO :FIRST_CREATE_DATE;

or

DECLARE VARIABLE tsEndDate TimeStamp;
begin
tsEndDate = :V_END_DATE || ' 23:59:59';


EXECUTE STATEMENT 'SELECT FIRST 1 D.CREATE_DATE
FROM DEBT D
WHERE D.CREATE_DATE <= ' || tsEndDate
INTO :FIRST_CREATE_DATE;

both caused this error.

ISC ERROR CODE: 335544569
ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 7, char 141
23

What am I doing wrong? Any work arounds?

Any help appreciated.

Thanks,
Mike