RE: INSERT INTO using a Common Table Expression

Maybe this from the SQL reference regarding CTE's applies to that error:

If a list of columns is specified, it must consist of as many names as
there are
columns in the result table of the fullselect.

Scott

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Gary Thompson
Sent: Friday, August 31, 2012 3:27 PM
To: RPG programming on the IBM i / System i
Subject: RE: INSERT INTO using a Common Table Expression

Sam,
Thank you!

Still have an error (SQL State: 42802 Vendor Code -117)
SQL0117 Statement contains wrong number of values.

So, due to your help, I am much closer...


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Sam_L
Sent: Friday, August 31, 2012 3:15 PM
To: RPG programming on the IBM i / System i
Subject: 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!

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