If the problem is that it doesn't return any rows (and not that the rows contain null values), then I would expect the reason to be that DOUBLE PRECISION is a floating point and not fixed point datatype. Floating point datatypes never contain exact values, e.g. what you think is 15.3 may be stored as 15.299999999999999532 in TableA and 15.3000000000000000135 in TableB. Since these two numbers aren't identical, they don't match your JOIN criteria.
Either change the type to a fixed point datatype like NUMERIC or DECIMAL (I'm uncertain whether or not that will work on Dialect 1 databases) or join on RANGE, e.g. JOIN TABLE2 B ON B.URN BETWEEN A.LINKURN - 0.0000001 AND A.LINKURN + 0.0000001 If you choose the latter, a side effect is that it forces the optimizer to have A before B in the plan. I think you can avoid this by also doing it the other way around, i.e. JOIN TABLE2 B ON B.URN BETWEEN A.LINKURN - 0.0000001 AND A.LINKURN + 0.0000001 AND A.LINKURN BETWEEN B.URN - 0.0000001 AND B.URN + 0.0000001 HTH, Set 2016-12-12 10:11 GMT+01:00 [email protected] [firebird-support] < [email protected]>: > > > There are no NULL values in either of the columns (that was my first > thought). The table structure is > > TABLEA > URN: VARCHAR(12) > LINKURN: VARCHAR(12) [Links to TABLEB.ID] > AMOUNT: DOUBLE PRECISION > > TABLEB > URN: VARCHAR(12) > VALUE: DOUBLE PRECISION > > so the query is > SELECT A.AMOUNT, B.VALUE, A.AMOUNT/B.VALUE > FROM TABLE1 A > JOIN TABLE2 B ON A.LINKURN = B.URN > > > > >
