Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
Conversion error |
Tue, Jan 1 2008 10:51 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Jan 1 2008 12:47 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Jan 2 2008 3:45 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Jan 2 2008 11:41 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Jan 2 2008 2:29 PM | Permanent Link |
"Ole Willy Tuv" | 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 |
Wed, Jan 2 2008 4:02 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |