My first 1.07 bug

SELECT _BoxNo, _BoxName,_Unread, _IsFolder
from "BandA"
WHERE CAST((_BoxNo / -100000) AS INTEGER) = 3
ORDER BY _BoxNo DESC

ElevateDB Error #1011 An error occurred with the value 0 (A conversion error occurred)


The values of _BoxNo are

-300009 to -300001 and 1 to 399

Roy Lambert

Roy,

<< SELECT _BoxNo, _BoxName,_Unread, _IsFolder
from "BandA"
WHERE CAST((_BoxNo / -100000) AS INTEGER) = 3
ORDER BY _BoxNo DESC

ElevateDB Error #1011 An error occurred with the value 0 (A conversion
error occurred) >>

It's not a bug.  The issue is that you're causing the result of the
floating-point division to be truncated, thus you're loosing data.  You need
to use this instead:

SELECT _BoxNo, _BoxName,_Unread, _IsFolder
from "BandA"
WHERE CAST(TRUNC(_BoxNo / -100000) AS INTEGER) = 3
ORDER BY _BoxNo DESC

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I can understand your words but the logic behind them puzzles me. I would have thought the very fact that I was CASTing as INTEGER meant that I wanted to truncate the value. Unless this behaviour is mandated as part of the standard can we return to the way DBISAM worked please?

Roy Lambert

Tim

Slight improvement to your code


SELECT _BoxNo, _BoxName,_Unread, _IsFolder
from "BandA"
WHERE TRUNC(_BoxNo / -100000) = 3
ORDER BY _BoxNo DESC

No need for the CAST.

Roy Lambert

Tim,

<< It's not a bug.  The issue is that you're causing the result of the
floating-point division to be truncated, thus you're loosing data. >>

It looks like a bug to me. The general rule for casting an approximate
numeric to exact numeric is as follows:

TD = target data type
SD = source data type
TV = target value
SV = source value

- If there is a representation of SV in the data type TD that does not lose
any leading significant digits after rounding or truncating if necessary,
then TV is that representation. The choice of whether to round or truncate
is implementation-defined.

- Otherwise, an exception condition is raised: data exception — numeric
value out of range.

I don't think the CAST expression in Roy's query is causing any loss of
leading significant digits.

BTW, is ElevateDB supposed to truncate or round the result of a CAST to
exact numeric ?

Ole Willy Tuv

Ole,

<< It looks like a bug to me. The general rule for casting an approximate
numeric to exact numeric is as follows: >>

Yes, it appears that we can safely ignore any loss of digits after the
decimal point.

--
Tim Young
Elevate Software
www.elevatesoft.com