Windows 7/64bit, DB2 odbc drive and overflow

duhaas · #1 (**permalink**) 08-26-10, 07:24

I keep getting:

ERROR: [IBM][CLI Driver][DB2/NT64] SQL0413N Overflow occurred
during numeric data type conversion. SQLSTATE=22003

Query
= SELECT
acct.PI as AbacusID,
acct.PN,
acct.AM,

acct.T1,
acct.MG,
summ.DT,
summ.cc_code1,

cast(summ.TWRR as decimal(18,8)),
summ.*
FROM

PORTATTR acct
left join SUMMRET summ
on acct.PI = summ.PI
WHERE acct.PN = '9841M287003'

When i dont cast summ.TWRR its giving me the scientific representation of the number and I would prefer to see the actual number. Not sure if it has to do with me using an odbc driver or what, any suggestions would be great. I've tried increasing the decimal up and its not helping. New to DB2 so please go easy on me. Also,if i change the filter to in this case to a different account, sometime it will come back fine other not. I understand its due to the number exceeding the length of the decimal i'm casting, but if i dont know what the potential max could be I'm a bit lost

tonkuma · #2 (**permalink**) 08-26-10, 08:11

How about casting to decfloat?

Here is an example:

Code:

------------------------------ Commands Entered ------------------------------
SELECT double_float
     , DECFLOAT(double_float) AS cast_to_decfloat
  FROM (VALUES 1.23456789E+15 , 1.23456789E-5 ) AS d(double_float)
;
------------------------------------------------------------------------------

DOUBLE_FLOAT             CAST_TO_DECFLOAT                          
------------------------ ------------------------------------------
  +1.23456789000000E+015                           1234567890000000
  +1.23456789000000E-005                            0.0000123456789

  2 record(s) selected.

duhaas · #3 (**permalink**) 08-26-10, 08:31

Thanks TON, that allows the results to come back now. I actually tried DOUBLE_PRECISION and that seemed to work as well. just doesnt like decimal.

Internet Services

Web Development

Digital Marketing

Consumer Tech