Unexpected rounding when using ISNULL in sybase sql anywhere

Question

Hi there,

I have to work with a legacy system based on SQL Anywhere 5, db access layer is in C++ using embedded SQL. The problem I have is not really a C++ problem, but embedded SQL forces us to SQL statements which cannot be dynamically generated. There are many tables containing a lot of columns having the datatype double. Though the main application expects these columns to be not NULL, there are no NOT NULL contraints on most of this columns. To avoid application crashes, we use a lot of ISNULL in our SQLs. For example, instead of just writing

SELECT d1,d2,d3,d4 FROM table1;

we write

SELECT ISNULL(d1,0),ISNULL(d2,0),ISNULL(d3,0),ISNULL(d4,0) FROM table1;

So far, problem solved, everything seems fine - we thought. Now, we have found, that, when for example d1 contains 1.2345678987654321 in one row, then

SELECT d1 ...

results in

1.2345678987654321

but

SELECT ISNULL(d1,0) ...

results in

1.234567

The reason for this seems to be that when you call ISNULL with a double and a number like 0, there happens some type conversion of both arguments of ISNULL to NUMBER(30,6) first, so d1 is cutted to 6 decimals. Simple fix is here

SELECT ISNULL(d1,CAST(0 AS DOUBLE)) ...

This will give the expected 1.2345678987654321 again, but it results in very lengthy SQL statements:

SELECT ISNULL(d1,CAST(0 AS DOUBLE)),ISNULL(d2,CAST(0 AS DOUBLE)),ISNULL(d3,CAST(0 AS DOUBLE)), ISNULL(d4,CAST(0 AS DOUBLE))

So we tried to find a shorter way to write a DOUBLE zero literally in SQL:

SELECT ISNULL(d1,0e0)... or SELECT ISNULL(d1,0.0)...

does not work, gives 1.234567 again.

SELECT ISNULL(d1,SIN(0))... or SELECT ISNULL(d1,1e-307)...

works both, yields correct 1.2345678987654321, but seems both very ugly just for writing a 0.

So here is the question: any ideas on how to write this shorter / more readable / avoid the problem totally? Remember, neither generating SQL statements is an option under our constraints, nor is changing the DB schema.

Answer 1

no sure if it will work for v5 try to set the database option to treat floats as doubles via sql
run the query

set option FLOAT_AS_DOUBLE = 'ON'

if you want it to hold for the current connection only use

set TEMPORARY option FLOAT_AS_DOUBLE = 'ON'

you can then try to use your query's first version