With Clause result set in an select/insert statement

General
Enthusiast

With Clause result set in an select/insert statement

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

9 REPLIES
Junior Contributor

Re: With Clause result set in an select/insert statement

Simply concat all three columns:

INSERT INTO myTarget 
WITH delim AS
(SEL Trim(seqno)|| '~' || Trim(id) || '~' || param AS allCols
from mySource
) SEL * FROM TABLE(CSVLd(delim.allCols, '~','') RETURNS ( seqno VARCHAR(30), id VARCHAR(30), col1 VARCHAR(30), col2 VARCHAR(30), col3 VARCHAR(30), col4 VARCHAR(30), col5 VARCHAR(30), col6 VARCHAR(30)) ) AS t1;

 

 

Enthusiast

Re: With Clause result set in an select/insert statement

If I wanted to add a new column into the target table by joining with source table, where do I provide the join.

 

Throws error when I tried join within select statement.

 

In this example, assume seq_no is from other table source_2. Now I wanted to join on a column with source 1 and source 2 to fetch seqno.

Enthusiast

Re: With Clause result set in an select/insert statement

Thanks Dnoeth, as always you help for my doubts!

 

Let me put my question in this way for easy understanding.

 

Example:

 

TABLE NAME : SOURCE

SNOPARAMETER
1ABC01~BCD02~CDE03
2HG02~GT56~QW23
3RR56~TS32~T467

 

My requirement is, To split the parameter column into multiple columns say col1,col2,col 3 and insert into table DESTINATION.

In the below DESTINATION table, I ve one more column say ID which should be joined with SOURCE2 table.

 

TABLE NAME - DESTINATION: Expected o/p in DESTINATION table:

SNOIDCOL1COL2COL3
13ABC01BCD02CDE03
26HG02GT56QW23
37RR56TS32T467

 

Using below query for splitting the columns. But how do I select s.no here? And how do I select ID by joining with SOURCE 2 table.

 

When I try joining, it throws error stating, NON RECURSIVE WITH CLAUSE SHOULD NOT REFERENCE ITSELF WITH OWN DEFINITION.

SyntaxEditor Code Snippet:

WITH SOURCE AS(SEL SOURCE.PARAMETER AS PARAM)SEL * 
FROM TABLE(CSVLD    (SOURCE.PARAM, '~','')RETURNS (COL1 VARCHAR(255) CHARACTER SET LATIN,COL2 VARCHAR(255) CHARACTER SET LATIN,COL3 VARCHAR(255) CHARACTER SET LATIN)AS T1;

 

Junior Contributor

Re: With Clause result set in an select/insert statement

There's no FROM in your Common Table Expression:

WITH AS
 ( SEL Trim(t1.sno)|| '~' || Trim(t2.id) || '~' || t1.PARAMETER AS PARAM 
   FROM myTable as t1
   JOIN myOtherTable as t2
   ON ...
 )
Enthusiast

Re: With Clause result set in an select/insert statement

getting error as, Expected something between with and as keyword.

 

If I give SOURCE table name between WITH and AS keyword (query as below), getting error as, "Non recursive with clause shouldnot have reference within own definition".

 

WITH mytable AS ( SEL Trim(t1.sno)|| '~' || Trim(t2.id) || '~' || t1.PARAMETER AS PARAM 
   FROM myTable as t1
   JOIN myOtherTable as t2
   ON ...
 )

Now, when I give T1 between with and s as in below query,

WITH t1 AS ( SEL Trim(t1.sno)|| '~' || Trim(t2.id) || '~' || t1.PARAMETER AS PARAM 
   FROM myTable as t1
   JOIN myOtherTable as t2
   ON ...
 )

It just returns only the column seperated by delimiters in CSVld function and not the sno,id column in resultset.

 

Highlighted
Junior Contributor

Re: With Clause result set in an select/insert statement

Name it whatever you want, just don't use that name in the WITH part, otherwise the parser assumes a recursive query.

 

INSERT INTO myTarget 
WITH whatever AS
 ( SEL Trim(t1.sno)|| '~' || Trim(t2.id) || '~' || t1.PARAMETER AS PARAM 
   FROM myTable as t1
   JOIN myOtherTable as t2
   ON ...
 )
SEL *
FROM TABLE(CSVLd(whatever.PARAM , '~','')
RETURNS (
seqno VARCHAR(30),
id    VARCHAR(30),
col1  VARCHAR(30),
col2  VARCHAR(30),
col3  VARCHAR(30),
col4  VARCHAR(30),
col5  VARCHAR(30),
col6  VARCHAR(30))
) AS t1;

 

 

Enthusiast

Re: With Clause result set in an select/insert statement

Thanks,Got it. but still, getting an error stating 'Input data have a string column with no ending quote character'.

 

Does it mean datatype does not match b/e i/p and return type? If yes, can I cast it here in return stt?

 

EDITED TO ADD:

Casting doesnot work

Junior Contributor

Re: With Clause result set in an select/insert statement

'Input data have a string column with no ending quote character' is not a DBMS error message.

 

Of course you can cast the returned columns to the correct data type.

 

 

Teradata Employee

Re: With Clause result set in an select/insert statement

 

 

...
SEL CAST(SeqNo as INTEGER), CAST(ID as INTEGER), Col1, Col2, Col3, Col4, Col5, Col6 FROM TABLE(CSVLd ...