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).