How to resolve the numeric overflow error in Teradata
Contents
Numeric Datatypes in Teradata
Teradata provides the multiple numeric data types for the columns and it is listed below
- Byte Int – Represents a signed binary integer value in the range -128 to 127.
- Small Int – Represents a signed binary integer value in the range -32768 to 32767.
- Integer – Represents a signed, binary integer value from -2,147,483,648 to 2,147,483,647.
- Big Int – Represents a signed, binary integer value from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
- Float/Double – Represent values in sign/magnitude form ranging from 2.226 x 10-308 to 1.797 x 10308.
- Number – Represents a numeric value with optional precision(range is from 1 to 38) and scale limitations.
- Decimal – Represents a decimal number of n digits( range is from 1 through 38), with m(the number of fractional digits) of those n digits to the right of the decimal point.
Numeric overflow error (code = 2616) in Teradata
If we try to insert the larger values than the allowed/specified size of the numeric column, Teradata will throw the numeric overflow error. Lets see the numeric overflow error for Decimal column with example.
Example : Target table ==> Banking_DB.Customer
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE SET TABLE Banking_DB.CUSTOMER ,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO, MAP = TD_MAP1 ( Cust_Id INTEGER, Cust_Name CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC, Join_Date DATE FORMAT 'YYYY/MM/DD', Mobile_Number INTEGER, Login_Count DECIMAL(8,0) ) UNIQUE PRIMARY INDEX ( Cust_Id ); |
Here the customer table is created in the Banking database. The table contains a column as Login_count that specified to Decimal(8,0). If we try to insert the value more than 8 digits for Login_count, It will throw the Numeric overflow error.Initially the table contains the two records as below.
Example : Source table ==> Banking_DB.Customer_old
The source table Customer_old contains the old and new customer details with login count. The structure and the values of the table has mentioned below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE MULTISET TABLE Banking_DB.customer_old ,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO, MAP = TD_MAP1 ( Cust_Id INTEGER, Cust_Name CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC, Join_Date DATE FORMAT 'YYYY/MM/DD', Mobile_Number INTEGER, Login_Count DECIMAL(8,0)) NO PRIMARY INDEX ; |
The insert query is trying to select and insert the record with the login count as 18292892 + 19292929982 for the customer id=455. Since the login count value is exceeds the 8 digits during computation in the SELECT CASE statement, it is throwing the numeric overflow error as below.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
INSERT INTO BANKING_DB.CUSTOMER SELECT CUST_ID, CUST_NAME, JOIN_DATE, MOBILE_NUMBER, CASE WHEN CUST_NAME='John' THEN LOGIN_COUNT+19292929982 -- adding some values to the login count column ELSE 0 END AS LOGIN_COUNT FROM BANKING_DB.CUSTOMER_OLD WHERE CUST_ID = 455; |
Output of the insert statement
*** INSERT Failed 2616 Numeric overflow occurred during computation. ***
Resolution: Up size the decimal column in Teradata
We can up size the Login_count column from Decimal(8,0) to Decimal(38,0) in the Target table that will resolve the numeric overflow error during computation.Lets up size the column in Customer table and run the insert query again
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DROP TABLE BANKING_DB.CUSTOMER; CREATE SET TABLE PP_SCRATCH.CUSTOMER ,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO, MAP = TD_MAP1 ( Cust_Id INTEGER, Cust_Name CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC, Join_Date DATE FORMAT 'YYYY/MM/DD', Mobile_Number INTEGER, Login_Count DECIMAL(38,0)) -- Upsized the column to Decimal(38,0) UNIQUE PRIMARY INDEX ( Cust_Id ); |
The record for cust_id=455 has inserted into the customer table and it contains the Login_count value with more than 8 digits.
Recommended Articles
