Re: Can I do this in SQL? How?

<QUOTE/>
But it does mean creating a temporary
table and that's one of the things I'd like to avoid - the temporary
file is just one more object that potentially requires maintenance when
the business logic changes.
</QUOTE>

I with you in that regard, as it seems an unnecessary (kludgy?) work around.
Who knows? Maybe in a future version of SQL UPDATE will allow CTEs...


Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Wed, Feb 10, 2010 at 9:33 AM, Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>wrote:

Luis Rodriguez wrote:

Birgitta,

Ran your example with the *SYN (Syntax checker) option of SQL, without
problems. In fact, the V6R1 SQL Reference states that

<QUOTE/>

The *table name *of a common table expression can only be referenced in

the

*select-statement*, INSERT statement, or CREATE VIEW statement that

defines

it.

</QUOTE>
A PTF needed, perhaps?


Regards,
Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries

So it looks like I can do an INSERT from a CTE, but not an UPDATE.
That's not horrible; I could create a temporary table and then use that
in an UPDATE without too much trouble. Maybe that's the reasoning
behind not supporting UPDATE. But it does mean creating a temporary
table and that's one of the things I'd like to avoid - the temporary
file is just one more object that potentially requires maintenance when
the business logic changes.

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