Hi,
I am (EVENTUALLY), getting around to upgrading from Firebird 2.5 to Firebird 3.
I purchased the conversion guide, and so far so good, except for this problem,
which I cannot find a mention of anywhere, no online, nor in conversion the
guide:
SELECT C.ENDDATE
FROM CMN_PERIODS C
WHERE (C.PERIOD = mod(:IPeriod, 100))
AND (C.YR = (:IPeriod / 100))
The error is:
SQL error state =42000
Expression evaluation not supported.
Invalid data type for division in dialect 3
Eventually, I figured out that this possibly happens on the Firebird 3 server
when :IPeriod is null, since this now works:
SELECT C.ENDDATE
FROM CMN_PERIODS C
WHERE (C.PERIOD = mod(:IPeriod, 100))
AND (C.YR = (coalesce(:IPeriod) / 100))
BUT, this also works! (ie. Does not give an error)
SELECT C.ENDDATE
FROM
CMN_PERIODS C
WHERE
(C.PERIOD = mod(:IPeriod, 100))
AND
(C.YR = (null / 100))
I am doing his via FastReports and FIBPlus datasets, so not sure if that is
affecting things. The parameter type is set to Integer.
I am going to go ahead and add coalesce to my 20 queries that have this in
various reports, but would like to know if this is an intended side-effect (and
my Google-Fu just not good enough to find the relevant documentation), and/or
if there is some other better way I should be dealing with this problem rather
(since I am not 100% understanding what has changed and why).
Thanks
Maya
