Re: INSERT INTO using a Common Table Expression

Here's a working example.

First, create a target table in QTEMP:

create table qtemp/xxx as (select city,state from qiws/QCUSTCDT)
with no data

Now insert some data, using a CTE:

insert into qtemp/xxx with cte as (select city ccc, state sss from
QIWS/QCUSTCDT) select ccc, sss from cte

12 rows inserted in XXX in QTEMP.

select * from qtemp/xxx

CITY STATE
Dallas TX
Clay NY
Broton VT
Helen GA
Hector NY
Denver CO
Isle MN
Sutter CA
Casper WY
Dallas TX
Hector NY
Isle MN

Done from STRSQL greem screen on a V5R3 machine.

Sam

On 8/31/2012 4:58 PM, Gary Thompson wrote:

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!