Re: SQL - CTE - Insert

Thanks Loyd...that explains why I can't do inserts.

On 10/4/06, lgoodbar@xxxxxxxxxxxxxx <lgoodbar@xxxxxxxxxxxxxx> wrote:

As of V5R2, CTEs can be used only for selects. I too would like the use
CTEs
for deletes as well as inserts. It could be (at least with deletes) that
using a CTE means using a copy of the data instead of the data itself; so
the references required to perform a delete do not exist. Not sure why the
restriction on inserts.

Loyd Goodbar
Senior programmer/analyst
BorgWarner
E/TS Water Valley
662-473-5713

-----Original Message-----
From: Michael Ryan [mailto:michaelrtr@xxxxxxxxx]
Sent: Wednesday, October 04, 2006 08:12
To: Midrange Systems Technical Discussion
Subject: SQL - CTE - Insert

Well, looking at Charles example of a CTE, I thought I would try this:

with stuff as (Select xmmail, xmsmn1, min(xmcdte) From posmstp
Where xmmail <> ' ' Group by xmmail, xmsmn1) INSERT INTO
NEWSYS/EMLslsP (Esemp#, Esemail, esactive, escred) from (select * from
stuff)

But I get this error:
Keyword INSERT not expected. Valid tokens: ( SELECT.

This works fine:

with stuff as (Select xmmail, xmsmn1, min(xmcdte)
               From posmstp
               Where xmmail <> ' '
               Group by xmmail, xmsmn1)
select xmmail, xmsmn1 from stuff

Does an Insert not work with a CTE like this?

Also, prompting isn't supported when I use a CTE - is that just the way it
is?
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.