Splitting delimited columns to seperate columns

General
Enthusiast

Re: Splitting delimited columns to seperate columns

Thanks Fred and Dieter, it worked. Now, with another clarification.

 

I have a table in below format.

MY TARGET TABLE HAS BELOW COLUMNS:
SEQ_NO,
ID,
COL1,
COL2,
COL3,..

PRIMARY INDEX AS SEQ_NO, ID

My source table(delim) has below columns:
seq_no,
id,
param

I have derived col 1 to col n using csvLD function with query as below to split the values: Used only the param column and not other two for splitting delimited columns.

WITH delim AS
(SEL 'ab12~ab34~ab56~ab78~ab90~ab01' AS param)
SEL *
FROM TABLE(CSVLd(delim.param, '~','')
RETURNS (
col1 VARCHAR(30) CHARACTER SET latin,
col2 VARCHAR(30) CHARACTER SET latin,
col3 VARCHAR(30) CHARACTER SET latin,
col4 VARCHAR(30) CHARACTER SET latin,
col5 VARCHAR(30) CHARACTER SET latin,
col6 VARCHAR(30) CHARACTER SET latin)
) AS t1;

Now, I wanted to insert the above result set into target table. Please suggest an efficient way of doing it since expecting millions of records for a day.

TO Note: Target table structure shouldnot be changed.
Below query doesnot work, throws me an error:

WITH delim AS
(SEL 'ab12~ab34~ab56~ab78~ab90~ab01' AS param)
SEL *
FROM TABLE(CSVLd(delim.param, '~','')
RETURNS (
col1 VARCHAR(30) CHARACTER SET latin,
col2 VARCHAR(30) CHARACTER SET latin,
col3 VARCHAR(30) CHARACTER SET latin,
col4 VARCHAR(30) CHARACTER SET latin,
col5 VARCHAR(30) CHARACTER SET latin,
col6 VARCHAR(30) CHARACTER SET latin)
) AS t1
sel
delim.seq_no,
delim.id,
t1.col1,
t2.col2,
...
from delim

Teradata Employee

Re: Splitting delimited columns to seperate columns

As @dnoeth showed in your other thread, concatenate the SeqNo/ID columns and some ~ delimiters to the PARAM column, so you can have CSVLD return them (as VARCHAR).

Enthusiast

Re: Splitting delimited columns to seperate columns

I have the same question but could not find the other thread where additional values were concatenated and split using CSVLD. 

Anyone know how to locate that other thread? 

Thanks!

Enthusiast

Re: Splitting delimited columns to seperate columns