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