RE: Can I do this in SQL? How?

Joe,

I know what you mean, but it can be done if you introduce a View: -

Create View QTEMP/MYORDS as
with ORDSELECT as (select ORDCUST, ORDERNUM from ORDERS where
-- some complex criteria --),

CUSTSELECT as (select CUSTNUM from CUSTOMERS where
-- some more complex criteria --),

PURGEABLEORDERS as (select ORDERNUM from
ORDSELECT join CUSTSELECT on ORDCUST = CUSTNUM where
-- some final selection criteria --)

Select * from PURGEABLEORDERS;

update ORDERS set ORDPURGED = 'Y' where
ORDERNUM in (select ORDERNUM from QTEMP/MYORDS);

Sean


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: 09 February 2010 23:54
To: Midrange Systems Technical Discussion
Subject: Re: Can I do this in SQL? How?

McGovern, Sean wrote:

But can't you create MYFILE view as

WITH T1 AS (SELECT...)

and then update MYFILE ?

I wasn't being very clear. Got too frazzled with the Windows vs. i
thread.

Let me try to be more concise. What I want is this:

with ORDSELECT as (select ORDERNUM from ORDERS where
-- some complex criteria --),

CUSTSELECT as (select CUSTNUM from CUSTOMERS where
-- some more complex criteria --),

PURGEABLEORDERS as (select ORDERNUM from
ORDSELECT join CUSTSELECT on ORDCUST = CUSTNUM where
-- some final selection criteria --)

update ORDERS set ORDPURGED = 'Y' where
ORDERNUM in (select ORDERNUM from PURGEABLEORDERS)

Now I'm sure with enough work I could get all the criteria for the
ORDSELECT and CUSTSELECT and PURGEABLEORDERS subselects into the update
statement, but it would be a mess and more importantly, almost
impossible to debug. Instead, with this I can test each subselect to
make sure it works as intended and then finally put it all together.
And I do this all the time on complex queries. However, the problem is
that the UPDATE statement is not allowed after the definition of the
CTEs; all I can do is a final SELECT.

And I'm not sure why it is that way, but that's the way it is.

Joe