I have been researching the STRTOK_SPLIT_TO_TABLE at:
Is there away to add more columns to the select statement output along with the tokens. If so how does the syntax go?
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
The column 'item_list' is varchar, with format
The column 'Item_id' is decimal, with format
Thanks in advance!
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;
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.
I have a scenerio where i've to count the words contained in a sentece, senetece can be very long and of variable lenth, Data is like below separted by ,
I want to display like below:
Tried using STRTOK_SPLIT_TO_TABLE function and it works really well for small senetece like above but giving only half of the value for too long sentece.
Tried below query:
CREATE TABLE db.table1 AS (
SEL cast(X.OUTKEY AS DECIMAL(38,0)) ID , TOKENNUM as position_in_sentence , TOKEN as region from
(SELECT D.* FROM TABLE (STRTOK_SPLIT_TO_TABLE( CAST(table2.ID AS VARCHAR(38)), OTRANSLATE(table2.REGION_LIST,'',''), ',')
RETURNS (OUTKEY VARCHAR(38) , TOKENNUM INTEGER, TOKEN VARCHAR(20)CHARACTER SET UNICODE) ) AS D ) X group by 1,2,3) WITH DATA ;
Is there anyway to get the full value displayed from a long sentence basically I can count those words if I'am able to displaye on table level.
Perhaps it's due to the OTRANSLATE and not the STRTOK_SPLIT_TO_TABLE? Some sites have old (and often more limited) versions of functions such as OTRANSLATE installed in SYSLIB. Try explicitly qualifying TD_SYSFNLIB.OTRANSLATE(...).
(And if that works, talk to your DBA about possibly removing the old function.)
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:
FROM TABLE (strtok_split_to_table(DESDTCBS.KAE.serialno, DESDTCBS.KAE.AcctBalChgList, ';')
( serialno integer
, rownum integer
, new_col varchar(100)character set unicode)
) as d
Output result set:
I have SQL server function as below and is called wthin a view, can we implement similar logic in a function in Teradata? If not then is there any alternative?
CREATE FUNCTION [dbo].[SplitStrings] (
RETURNS @T TABLE (Value INT, ID INT)
--SET @String = @String + ';'
SET @String = @String + @delimiter
declare @Pos int
declare @Value int
declare @s varchar(20)
declare @long int
set @long = 1
WHILE len(@String) <> 0
set @Pos = charindex(@delimiter,@String)
IF @Pos=0 BREAK
set @s = substring(@String,1,@Pos-1)
-- insure we actually have a value before we insert in the table
if ltrim(rtrim(@s)) <> ''
INSERT @T (Value, ID) VALUES (cast(@s as int), @long)
SET @String = substring(@String,@Pos+1,8000)
SET @long = @long+1
I have a table with below two columns with their comma seperated row values.
I want to transform the comma seperated values of Vol_Str to column values as given below
Is there any function in teradata which could be used to achive this transformation
Thank you for help.
Can some one help me with this logic
empID EmailId Product
1234 email@example.com A,B,C
EmpID EmailID Product
1234 firstname.lastname@example.org C,D
Join above tables with empid and mailid and update TableA like below
empID EmailId Product
1234 email@example.com A,B,C,D
the values in product should be unique.
Can you please help me to get the WITH RECURSIVE query to divide the data from rows to columns(I think there is a strtok_split function, but need it using WITH RECURSIVE)
attached image is the sample data
I need your help in one scenario.
I have one table contain CMSL coulumn field which has set of values saparated by comma.
I need output rows equal to list of comma saparated values.
Table is as below
output should be as below
How to achieve this using teradata SQL query?
Please give me the suggestion.
I would like some help to convert a single row into multiple rows
I have something like this
store | item | quantity
001 | A | 4
001,002 | B | 5
004,006,003 | C | 12
And i would like to do something like this
001 | A | 4
001 | B | 5
002 | B | 5
004 | C | 12
006 | C | 12
003 | C | 12
I tried with regexp_split_to_table function but didn't manage to do what i need
Thx for your help :)
I think you'd better try with STRTOK_SPLIT_TO_TABLE().
SEL A.TOKEN1 AS STORE,B.ITEM,B.QUANTITY FROM
(sel D.OUTKEY,D.TOKEN1 FROM TABLE (STRTOK_SPLIT_TO_TABLE(T2.ITEM,T2.STORE,',')
RETURNS (OUTKEY VARCHAR(10),TOKENNUM INTEGER,TOKEN1 VARCHAR(20) CHARACTER SET UNICODE)) AS D
GROUP BY 1,2
) A,T2 B