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
