2616 : Numeric overflow error during computation

Database
Enthusiast

2616 : Numeric overflow error during computation

Hi,

I am getting the error "2616 : Numeric overflow error during computation" while executing the below code. Please help me to get resolved.
---------------
DECLARE strLveEvts VARCHAR(200);
SET strLveEvts = TRIM(strLveEvts) || ',' || TRIM(LveEvt);
/* where LveEvt is the field value which is numeric datatype */

DECLARE CurTest CURSOR FOR
SELECT A_AVL_PTC_WK FROM FMA_DDBO.TFMA170_PRDPTCSTA WHERE I_LVE_EVT IN (strLveEvts) FOR READ ONLY;
---------------

Which means if strLveEvts has single value, there is no error. If it has more than one values, i am getting the error due to string value. I simulated the same error in query window.

Example for simulating the error:

SET strLveEvts = '18031,19820';
SELECT A_AVL_PTC_WK FROM FMA_DDBO.TFMA170_PRDPTCSTA WHERE I_LVE_EVT IN (strLveEvts) FOR READ ONLY

If i hardcode the values i am not getting the error.
SELECT A_AVL_PTC_WK FROM FMA_DDBO.TFMA170_PRDPTCSTA WHERE I_LVE_EVT IN (18031,19820) FOR READ ONLY

Thanks,
Uma Kathir
4 REPLIES
Teradata Employee

Re: 2616 : Numeric overflow error during computation

No, if you "hardcode the values", the SQL statement would actually read:

SELECT A_AVL_PTC_WK FROM FMA_DDBO.TFMA170_PRDPTCSTA WHERE I_LVE_EVT IN ('18031,19820') FOR READ ONLY

Since I_LVE_EVT is numeric, Teradata attempts to implicitly cast the single string value '18031,19820' to the same type, which is causing the overflow.

It looks like you essentially want to use dynamic SQL (call dbc.SysExecSQL), but you can't declare a cursor for or within dynamic SQL...

A couple possibilities come to mind:
1) Use dynamic SQL to INSERT/SELECT any matching values to a work table (e.g. populate a materialized instance of a global temporary table), then declare a CURSOR to select from that work table.
2) Build your string with leading / trailing delimiters, e.g. '~18031~19820~' and use POSITION function for the match, such as

WHERE POSITION('~'||TRIM(I_LVE_EVT)||'~' IN strLveEvts) > 0

Enthusiast

Re: 2616 : Numeric overflow error during computation

Thank you so much.
I tried using both the methods and got success. I am wondering that which method would give better performance among them.

Thanks,
Uma Kathir
Teradata Employee

Re: 2616 : Numeric overflow error during computation

In most cases I would recommend the first option; but the second may actually perform better if the table is small or a high percentage of the rows match.
Enthusiast

Re: 2616 : Numeric overflow error during computation

Okay...
Thank you.