Saturday, October 27, 2012

2616: Numeric overflow occurred during computation.

In Teradata normally we come across this error while selecting column value which is too large to be accommodated in requested datatype.

 In below example we tried to count a  table which has more than 6 billion records, so certainly the count would return result in integer and Standard max value for integer data type is 2147483647. so whenever selected value exceeds this range it will throw this error.

select count(*)  from bm_tb.cust_priscription;

SELECT Failed. 2616: Numeric overflow occurred during computation. 

In this case to get the proper result we need to cast the returned integer value to float or decimal.

select count(*)  (float) from bm_tb.cust_priscription;

6,806,668,046.00

Or

select count(*)  (DECIMAL(12,0))  from bm_tb.cust_priscription;

6,806,668,046


You can try this demo to understand integer limit in SQLA:

SELECT CAST( 2147483647 AS INT)

Result: 2147483647

Then try to select integer value greater than the integer limit.

SELECT CAST( 2147483648 AS INT)

Result: "SELECT Failed. 2616: Numeric overflow occurred during computation."


No comments:

Post a Comment