What causes error "Strings cannot be added or subtracted in dialect 3"-CodePudding

I have the query:

WITH STAN_IND
AS (
SELECT ro.kod_stanow, ro.ind_wyrob||' - '||ro.LP_OPER INDEKS_OPERACJA, count(*) ILE_POWT
FROM M_REJ_OPERACJI ro
JOIN M_TABST st ON st.SYMBOL = ro.kod_stanow
WHERE (st.KOD_GRST starting with 'F' or (st.KOD_GRST starting with 'T')  )  AND ro.DATA_WYKON>'NOW'-100
GROUP BY 1,2)

SELECT S.kod_stanow, count(*) ILE_INDEKS, SUM(ILE_POWT-1) POWTORZEN
from STAN_IND S
GROUP BY S.kod_stanow
ORDER BY ILE_INDEKS

That should be working, but I get an error:

SQL Error [335544606] [42000]: Dynamic SQL Error; expression evaluation not supported; Strings cannot be added or subtracted in dialect 3 [SQLState:42000, ISC error code:335544606]

I tried to cast it into bigger varchar but still no success. What is wrong here? Database is a Firebird 2.1

CodePudding user response：

Your problem is 'NOW'-100. The literal 'NOW' is not a date/timestamp by itself, but a CHAR(3) literal. Only when compared to (or assigned to) a date or timestamp column will it be converted, and here the subtraction happens before that point. And the subtraction fails, because subtraction from a string literal is not defined.

Use CAST('NOW' as TIMESTAMP) - 100 or CURRENT_TIMESTAMP - 100 (or cast to DATE or use CURRENT_DATE if the column DATA_WYKON is a DATE).