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