ERROR 2616 Numeric overflow during computation while doing count(*)

Question

I am trying to do a select count(*) from table from a table and I am not able to do it because of this error. I am not aware of the number of rows in the table.

I am not doing any other aggregation in my query apart from this.

I guess it has something to do with the count value which is too large to be stored in INTEGER.

What is the alternative?

Answer 1

When your session runs in Teradata mode the result of a COUNT is INTEGER as you already noticed (in ANSI mode it will be a DECIMAL with at least 15 digits).

The workaround is simple, cast it to a bigint:

SELECT CAST(COUNT(*) AS BIGINT)...

Answer 2

This is the #2 Google hit for Teradata 2616, so I want to add something. If you're getting 2616 "Numeric overflow occured" from a SUM in Teradata, the solution is to CAST, then SUM. The CAST has to be inside the SUM:

SELECT SUM(CAST(WHATEVER_QTY AS DECIMAL(38,0))) FROM TER_DATABASE.WHATEVER_TABLE ;

In my case, DECIMAL(38,0) worked, but BIGINT was 2616. You are welcome to experiment. Here's the link at info.teradata:

http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1145_111A/Data_Type_Conversions.098.297.html