Subject Re: [firebird-support] Dividing one column by another
Author
Post date 2016-12-12T11:59:30Z
The link is not double precision columns, the link is on the VarChar columns.

Rows are returned and the first 2 columns show the TableA.Amount and and the TableB.Value.

The issue is the 3rd column (TableA.Amount / TableB.Value) always returns NULL.


---In firebird-support@yahoogroups.com, <setysvar@...> wrote :

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 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