error numeric overflow when using 'STRTOK_SPLIT_TO_TABLE'

Database
Enthusiast

error numeric overflow when using 'STRTOK_SPLIT_TO_TABLE'

Hi All,


When I run the code below, I encountered error 'Numeric overflow occurred during computation'.

My questions are:

1. I am not sure which step could result in this error possibly.

2. I'm also confused at the use case of function 'STRTOK_SPLIT_TO_TABLE'. It seems that there's is the max size limit for outkey which is 64 (reference from this document http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1145_111A/Stri...), what if the outkey I have is larger than that?

3. Is my mode efficient enough? If not, is there any suggestions to improve the query performance?

 

CREATE VOLATILE MULTISET TABLE SRP AS (
SEL A.INKEY,
	QT,
	TRMT_VRSN_ID,
	GUID,
	SESSION_START_DT,
	SESSION_SKEY,
	EVENT_TIMESTAMP,
	SEQNUM,
	PAGE_TYPE,
	KEYWORD,
	A.ITEM_LIST,
	N_ITEM,
	CIID AS JOIN_ID,   
    B.IDX,
    CAST(B.ITEM_ID AS DECIMAL(18, 0)) ITEM_ID
FROM SRP_VI_R A
INNER JOIN (
SEL *
FROM
  TABLE (STRTOK_SPLIT_TO_TABLE(SRP_VI_R.INKEY, SRP_VI_R.ITEM_LIST, ',')
  RETURNS (OUTKEY VARCHAR(64),
           IDX INT,
           ITEM_ID VARCHAR(50) 
)) AS DT 
) B
ON A.INKEY = B.OUTKEY
WHERE A.PAGE_TYPE = 'SRP'
) WITH DATA PRIMARY INDEX (QT, GUID, SESSION_START_DT, SESSION_SKEY) ON COMMIT PRESERVE ROWS;

The column 'inkey' is varchar, with format 

38752000005961500a606d4908640fcd140633681467593860008297

The column 'item_list' is varchar, with format

131703843351,281757007641,252366413917,131699736415,141838644205,371553670990,141817873515,281833457210,371430599653,351343383855,141742305231,171977054177,282028011819,301825332989,252239865331,182259933889,222151044811,112105840121,252368128501,162126956503,311464580799,141770434917,251932369253,321825219668,112109782316,201593905271,141827076139,272352627259,191881004350,262225858988,172180482007,141853364607,231804949168,172270079910,221681377532,331548284786,112112271015,231791949125,142016456067,301808765233,252516045478,361699819813,381218401788,172218753056,231493063381,401098111146,282083035157,141973396819,141813488199,252183852078

The column 'Item_id' is decimal, with format

371553670990

 

 

Thanks in advance!

 


Accepted Solutions
Junior Contributor

Re: error numeric overflow when using 'STRTOK_SPLIT_TO_TABLE'

TO_NUMBER returns NULL when input cannot be casted to a number, usually because of some bad data.

In your case you didn't get a bad data but a numeric overflow error, so your input data is numeric and TO_NUMBER doesn't fail, of course.

 

But the result contains more than 18 digits, thus TRYCAST returns NULL.

Increasing precision to DECIMAL(38,0) should work.

 

Regarding hashing there's a MD5 UDF found on Community download page, which returns a char32.

And there's an implementation of the faster and more modern BLAKE2 on bitbucket, but it returns BYTE(64) and thus has to be tweaked.

1 ACCEPTED SOLUTION
5 REPLIES
Junior Contributor

Re: error numeric overflow when using 'STRTOK_SPLIT_TO_TABLE'

Q1: There's probably some non-numeric data returned in OUTKEY, you can check using TO_NUMBER  which returns NULL for invalid data:

SEL *
FROM
  TABLE (STRTOK_SPLIT_TO_TABLE(SRP_VI_R.INKEY, SRP_VI_R.ITEM_LIST, ',')
  RETURNS (OUTKEY VARCHAR(64),
           IDX INT,
           ITEM_ID VARCHAR(50) 
)) AS DT 
WHERE TO_NUMBER(OUTKEY) IS NULL

Similar in TD15.10 using TRYCAST:

TRYCAST(B.ITEM_ID AS DECIMAL(18, 0))

 

Q2: The OUTKEY is truncated when it's longer than 64 characters.

 

Q3: You should apply the WHERE-condition before STRTOK: 

 

CREATE VOLATILE MULTISET TABLE SRP AS (
WITH cte AS
 ( SELECT INKEY, ITEM_LIST
   FROM SRP_VI_R
   WHERE PAGE_TYPE = 'SRP'
 )
SEL A.INKEY,
	QT,
	TRMT_VRSN_ID,
	GUID,
	SESSION_START_DT,
	SESSION_SKEY,
	EVENT_TIMESTAMP,
	SEQNUM,
	PAGE_TYPE,
	KEYWORD,
	A.ITEM_LIST,
	N_ITEM,
	CIID AS JOIN_ID,   
    B.IDX,
    CAST(B.ITEM_ID AS DECIMAL(18, 0)) ITEM_ID
FROM SRP_VI_R A
INNER JOIN (
SEL *
FROM
  TABLE (STRTOK_SPLIT_TO_TABLE(cte.INKEY, cte.ITEM_LIST, ',')
  RETURNS (OUTKEY VARCHAR(64),
           IDX INT,
           ITEM_ID VARCHAR(50) 
)) AS DT 
) B
ON A.INKEY = B.OUTKEY
WHERE A.PAGE_TYPE = 'SRP'
) WITH DATA PRIMARY INDEX (QT, GUID, SESSION_START_DT, SESSION_SKEY) ON COMMIT PRESERVE ROWS;

 

 

Enthusiast

Re: error numeric overflow when using 'STRTOK_SPLIT_TO_TABLE'

Hi Dieter,

 

Thanks for the detailed explanation, that helps a lot!

 

But I am still confused at my second question, since the outkey is truncated at the 64th character, if the outkey I need is longer than 64 characters, is there any method , like one to one hash function that I can convert the outkey to some string less than 64 character?

I used to try to use the row number as the unique outkey, but since the data is too large, olap function will generate 'no more spool space' error.

 

Thanks a lot!

Enthusiast

Re: error numeric overflow when using 'STRTOK_SPLIT_TO_TABLE'

Hi Dieter, as for the 'numeric overflow' problem, when I replace

CAST(B.ITEM_ID AS DECIMAL(18, 0))

to 

TRYCAST(B.ITEM_ID AS DECIMAL(18, 0))

the error disapprears. My question is,

1. in my data, ITEM_ID is either numeric or null, cast will cast NULL to NULL, trycast also cast NULL to NULL, why does trycast work here but cast does not?

2. in my previous jobs, the code with 'cast' runs fine even when some of the item_id are NULL, does the error 'numeric overflow' happen when there are too many NULLs?

 

Thanks!

Junior Contributor

Re: error numeric overflow when using 'STRTOK_SPLIT_TO_TABLE'

TO_NUMBER returns NULL when input cannot be casted to a number, usually because of some bad data.

In your case you didn't get a bad data but a numeric overflow error, so your input data is numeric and TO_NUMBER doesn't fail, of course.

 

But the result contains more than 18 digits, thus TRYCAST returns NULL.

Increasing precision to DECIMAL(38,0) should work.

 

Regarding hashing there's a MD5 UDF found on Community download page, which returns a char32.

And there's an implementation of the faster and more modern BLAKE2 on bitbucket, but it returns BYTE(64) and thus has to be tweaked.

Enthusiast

Re: error numeric overflow when using 'STRTOK_SPLIT_TO_TABLE'

Thanks Dieter!