strtok_split_to_table support only Integer and does not support other data type

Database
Teradata Employee

strtok_split_to_table support only Integer and does not support other data type

I have 2 questions regard to this function

1. Did this function able to support any Numeric and Character data type as it said in the SQL Function ... document? From the test, seem like the outkey support data type is only INTEGER. I try to use another data type but it return me error message "SELECT Failed. 9134:  STRTOK:  Inconsistent data types or data character sets among inputs and outputs."

2. From the SQL Function document, seem like inkey and outkey are the same value. But when I tested, it return data differently. I don't know where the outkey that its return coming from.

Below is the table input:

serialno|subsequence|AcctBalChgList

1817840|0|11446332,13002321,201,0,1700,2000,101,20130501;11446332,13002400,210,500,0,2400,101,20130501;11446888,13002500,202,30,0,3000,401,20130401

1817839|0|11446332,13002321,201,0,1700,2000,101,20130501;11446332,13002400,210,500,0,2400,101,20130501

Query:

SELECT D.*

FROM TABLE (strtok_split_to_table(DESDTCBS.KAE.serialno, DESDTCBS.KAE.AcctBalChgList, ';')

RETURNS

( serialno integer

, rownum integer

, new_col varchar(100)character set unicode)

) as d

Output result set: 

serialno|rownum|new_col

239|2|11446332,13002400,210,500,0,2400,101,20130501

239|1|11446332,13002321,201,0,1700,2000,101,20130501

240|2|11446332,13002400,210,500,0,2400,101,20130501

240|1|11446332,13002321,201,0,1700,2000,101,20130501

240|3|11446888,13002500,202,30,0,3000,401,20130401

2 REPLIES
Senior Apprentice

Re: strtok_split_to_table support only Integer and does not support other data type

Q1: For a character inkey use "RETURNS (serialno varchar(..) character set unicode".

Q2: "Numeric" seems to be strictly INTEGER, nothing else. Otherwise you get garbage, just tried it on 14.00.02.07.

You should opem an incident to either fix the function or the manuals :-)

As a workaround you can CAST the numeric column to a VARCHAR.

Dieter

Junior Supporter

Re: strtok_split_to_table support only Integer and does not support other data type

Hi all.

The 'outkey' returned value must be, according to manuals, either INTEGER or VARCHAR(10), but I've found that it can be a VARCHAR up to 32 characters (exceeding this limit, you get error 9134 "STRTOK: Varchar Inkey exceeded the max. allowed inkey length. Redefine UDF."

HTH.

Cheers.

Carlos.