7

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?

Qix - MONICA WAS MISTREATED
  • 14,451
  • 16
  • 82
  • 145
user3055262
  • 405
  • 3
  • 9
  • 20

2 Answers2

11

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)...
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • 1
    I really wish Teradata mode would move up to BIGINT if they aren't going to fully comply with ANSI. Just like I wish Excel wouldn't choke on DECIMAL(18,0) or larger. :) – Rob Paller Jan 31 '14 at 02:27
  • ANSI doesn't talk about numeric precision, this is always "implementation-defined". If you want better compliance with ANSI you just have to switch session mode to ANSI. And Excel already chokes on DEC(16) as it's storing/processing everything as FLOAT :-) – dnoeth Jan 31 '14 at 06:54
  • No thank you. I'm quite content with the subtleties of Teradata mode. :) – Rob Paller Jan 31 '14 at 13:38
1

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

mojave
  • 21
  • 4