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