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 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?
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 firstname.lastname@example.org A,B,C
EmpID EmailID Product
1234 email@example.com C,D
Join above tables with empid and mailid and update TableA like below
empID EmailId Product
1234 firstname.lastname@example.org 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.
Need to split the column value onto separate variable columns or rows
Need to split the '|' delimited values onto separate subtrsings and process accordingly. The number of '|' can vary.
my understanding is that strtok_split_to_table function only works on TD 14+ versions, here we're asking for a method for TD 13..
You can try on this way
create volatile table MyTest
)primary index( clave )
on commit preserve rows;
insert into MyTest(1, 'Calle~General Mola~28057');
insert into MyTest(2, 'AvenidaValgame Dios11428058');
insert into MyTest(3, 'Travesía~Gaztambide~0~28059');
insert into MyTest(4, 'Calle~Toma Moreno~4~28056');
insert into MyTest(5, 'Calle~Ansia viva~9~28122');
insert into MyTest(6, 'Calle~Percebe~66~28265');
FROM TABLE (STRTOK_SPLIT_TO_TABLE(MyTest.clave, MyTest.chorizo, '~')
RETURNS (outkey INTEGER,
token VARCHAR(128) CHARACTER SET UNICODE)
) AS d
order by 1, 2