× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.