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
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;
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.
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;
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 ... )
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.
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;
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
'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.
...
SEL CAST(SeqNo as INTEGER), CAST(ID as INTEGER), Col1, Col2, Col3, Col4, Col5, Col6 FROM TABLE(CSVLd ...