[firebird-support] Dividing one column by another

[Russell Weetch [e-mail] [firebird-support]]

I am trying to divide one column in table A by another in table B, but it
just gives me null values. The query is

SELECT A.AMOUNT, B.VALUE, A.AMOUNT/B.VALUE
FROM TABLE1 A
JOIN TABLE2 B ON A.URN = B.URN

any ideas gratefully received.

Russell

[Louis Kleiman [e-mail] [firebird-support]]

We probably need to see the table structures, but NULL handling can be
tricky. You may want to do something along the lines of
Coalesce(A.AMOUNT, 0)/Coalesce(B.VALUE, 1) to get something back when
either of your source values is NULL.

Post by Russell Weetch ***@smxi.com [firebird-support]
I am trying to divide one column in table A by another in table B, but it
just gives me null values. The query is
SELECT A.AMOUNT, B.VALUE, A.AMOUNT/B.VALUE
FROM TABLE1 A
JOIN TABLE2 B ON A.URN = B.URN
any ideas gratefully received.
Russell

[[e-mail] [firebird-support]]

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

[Svein Erling Tysvær [e-mail] [firebird-support]]

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

Post by ***@smxi.com [firebird-support]
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

[[e-mail] [firebird-support]]

The links aren't on the double precision fields, they are on the VarChar(12) link fields.

The query does return rows and shows the AMOUNT from Table A and the VALUE from Table B. All of which have values. It is the calculated field (A.AMOUNT / B.VALUE) that returns null.

[Dimitry Sibiryakov [e-mail] [firebird-support]]

Post by ***@smxi.com [firebird-support]
The query does return rows and shows the AMOUNT from Table A and the VALUE from Table B.
All of which have values. It is the calculated field (A.AMOUNT / B.VALUE) that returns null.

Can you copy-paste some results from *isql text output* as an example?
--
WBR, SD.


------------------------------------

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-***@yahoogroups.com
firebird-support-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-***@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[[e-mail] [firebird-support]]

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-***@yahoogroups.com, <***@...> 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 ***@... mailto:***@... [firebird-support] <firebird-***@yahoogroups.com mailto:firebird-***@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 http://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

[[e-mail] [firebird-support]]

Hmm. I restarted everything and now it seems to be returning a result. I haven't managed to recreate the issue so, I'll leave this for now.

Many thanks for your input