Numeric Overflow error in teredata

Database

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 .

4 REPLIES
Junior Supporter

Re: Numeric Overflow error in teredata

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.
Enthusiast

Re: Numeric Overflow error in teredata

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).
Enthusiast

Re: Numeric Overflow error in teredata

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? ;/

Supporter

Re: Numeric Overflow error in teredata

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.