| Subject | Re: [firebird-support] Dividing one column by another |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2016-12-12T10:42:27Z |
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 russell@... [firebird-support] <firebird-support@yahoogroups.com>:
There are no NULL values in either of the columns (that was my first thought). The table structure isTABLEAURN: VARCHAR(12)LINKURN: VARCHAR(12) [Links to TABLEB.ID]AMOUNT: DOUBLE PRECISIONTABLEBURN: VARCHAR(12)VALUE: DOUBLE PRECISIONso the query isSELECT A.AMOUNT, B.VALUE, A.AMOUNT/B.VALUEFROM TABLE1 AJOIN TABLE2 B ON A.LINKURN = B.URN