Hi,
A common table expression can only be part of an SELECT statement but never
of an insert statement.
So the default syntax must be:
Insert Into WorkTable(Column List)
Select Statement;
If the SQL statement has to include a CTE, syntax must be as follows:
Insert Into WorkTable (Column List if needed)
With CTE ....
Select ...
;
... some times ago I had some problems with an Insert statement based on an
SELECT-Statement with a CTE and a WITH NC clause.
Your second Statement with a column list should work
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Gary Thompson
Gesendet: Friday, 31.8 2012 22:59
An: RPG programming on the IBM i / System i
Betreff: INSERT INTO using a Common Table Expression
I am attempting to change an existing SQPRPGLE program.
We are at V7R1.
The goal is to change an INSERT statement that loads a work file.
The original statement was a simple SELECT, but the new statement uses a
common table expression to get an additional column from a related table.
Joining to the new table in the original SELECT has not seemed possible
because the new table has multiple rows for each row of the original SELECT,
which does not meet requirements. (I get duplicate rows)
Creating the CTE provided a means of grouping data from the new table to get
the required single row.
Running the new SELECT that joins to the CTE provides the data desired
dataset, but adding an INSERT INTO clause raises an error.
The SQL Reference does not show an example of an INSERT INTO with a CTE, but
the INSERT statement is mentioned as one of the possible uses of a CTE.
The error is SQL State 42601, vendor code -199 Keyword INSERT not expected.
Valid tokens: ( SELECT VALUES)
I have tried both of the following:
WITH CTE1 (SELECT col1, col2, MAX(col9) AS maxcol9
FROM itemtable
WHERE col1 = :var1
AND col2 = :var2
AND col3 IN ('1A', '1R')
AND col4 = 999
GROUP BY col1, col2
)
INSERT INTO wktable1
<- first attempt
SELECT X.colA, X.colB, X.colC, SUM(Y.colD) AS sumcolD,
maxcol9
FROM tableX X JOIN tableY Y
ON X.colA = Y.colA
AND X.colB = Y.colB
WHERE X.colA = :var1
AND X.colB = :var2
GROUP BY X.colA, XcolB
INSERT INTO wktable1 <- second
attempt
WITH CTE1 (SELECT col1, col2, MAX(col9) AS maxcol9
FROM itemtable
WHERE col1 = :var1
AND col2 = :var2
AND col3 IN ('1A', '1R')
AND col4 = 999
GROUP BY col1, col2
)
SELECT X.colA, X.colB, X.colC, SUM(Y.colD) AS sumcolD,
maxcol9
FROM tableX X JOIN tableY Y
ON X.colA = Y.colA
AND X.colB = Y.colB
WHERE X.colA = :var1
AND X.colB = :var2
GROUP BY X.colA, XcolB
Thanks for any help!
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
