With your response, and others, I did "land among the stars".
Yes, the second statement did work.
Thank you Birgitta
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Saturday, September 01, 2012 2:10 AM
To: 'RPG programming on the IBM i / System i'
Subject: AW: INSERT INTO using a Common Table Expression
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.
--
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
