With Clause result set in an select/insert statement

[Phoenix]

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

[dnoeth]

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;

[Phoenix]

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.

[Phoenix]

Thanks Dnoeth, as always you help for my doubts!

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

Example:

TABLE NAME : SOURCE

SNO	PARAMETER
1	ABC01~BCD02~CDE03
2	HG02~GT56~QW23
3	RR56~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:

SNO	ID	COL1	COL2	COL3
1	3	ABC01	BCD02	CDE03
2	6	HG02	GT56	QW23
3	7	RR56	TS32	T467

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;

[dnoeth]

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

[Phoenix]

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.

[dnoeth]

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;

[Phoenix]

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

[dnoeth]

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

[Fred]

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