All Forums Database
mihirmody9 1 post Joined 12/10
17 Apr 2011
Numeric Overflow error in teredata

Hi guys please help me out.

My data type for a column is decimal (18,10). Now when i am trying to run a query for a period of entire year then i am getting numeric overflow error. this is because my digits to the left of decimal is shooting up 8 digits. Hence since it is unable to accomodate more than 8 digits to the left of decimal so its giving numeric overflow .

Now i also dont want to loose on precision to the digits on right. i.e i want 10 digits to the right. So i am unable to change data type as decimal (18,4 ) or something like that .

If I cast it as float then i am loosing on precision for the digits on right since it rounds up to 2 digits.

Please suggest me something so that my problem is solved and also i get the precision which i want .

CarlosAL 512 posts Joined 04/08
18 Apr 2011

Cast as DECIMAL(required,10):

BTEQ -- Enter your SQL request or BTEQ command:
CREATE MULTISET TABLE my_db.test04 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
X INTEGER NOT NULL,
mynum decimal(18,10)
)
PRIMARY INDEX ( X )
;

*** Table has been created.
*** Total elapsed time was 1 second.

BTEQ -- Enter your SQL request or BTEQ command:
insert into my_db.test04 values (1,99999999.9999999999);

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

BTEQ -- Enter your SQL request or BTEQ command:
insert into my_db.test04 values (2,99999999.9999999999);

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

BTEQ -- Enter your SQL request or BTEQ command:
select sum(mynum) from my_db.test04;

*** Failure 2616 Numeric overflow occurred during computation.
Statement# 1, Info =0
*** Total elapsed time was 1 second.

BTEQ -- Enter your SQL request or BTEQ command:
select sum( cast(mynum as decimal(20,10))) from my_db.test04;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

Sum(mynum)
----------------------
199999999.9999999998

HTH.

Cheers.

Carlos.

07 Jun 2011

You are not losing the precision when changing it to Float.

In sql assistant go to tools--> option --> Answerset --> Number of decimal Places to display for float column ( change this to the required number , in your case 10).

Regards
Chanchal Preet Singh

wicik 35 posts Joined 06/12
14 Mar 2013

hi there,,, unfortunetly I fight with similer problem ;P
select
   tab_id
  ,tab2
  ,tab3
  ,tab_id_2
  ,tab4
  ,tab5
  ,sum(data_table_uplink)    as total_data_table_uplink
  ,sum(data_table_downlink)  as total_data_table_downlink
  ,sum(data_table_UPLINK)     as total_data_table__UPLINK
  ,sum(data_table_DOWNLINK)   as total_data_table__DOWNLINK
  ,sum(data_table__DURATION)      as total_data_table__DURATION
 from DB_table.SomeTable
 group by 1,2,3,4,5,6
 
It is a part of bigger select but this part returns me  Select Failed. 2616: Numeric overflow occured during computation.
Any sugestions? ;/
 
 

ulrich 816 posts Joined 09/09
14 Mar 2013

check the data types of 
data_table_uplink 
data_table_downlink
data_table_UPLINK
data_table_DOWNLINK
data_table__DURATION
It looks like the sums are exceding the maximum values for at least one of the colums.
Lets assume they are integers
in this case 
  ,sum(cast(data_table_uplink as decimal(18,0)))    as total_data_table_uplink
  ,sum(cast(data_table_downlink as decimal(18,0)))  as total_data_table_downlink
  ,sum(cast(data_table_UPLINK as decimal(18,0)))     as total_data_table__UPLINK
  ,sum(cast(data_table_DOWNLINK as decimal(18,0)))   as total_data_table__DOWNLINK
  ,sum(cast(data_table__DURATION as decimal(18,0)))      as total_data_table__DURATION
is likely to work.
 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.