Re: Can I do this in SQL? How?

McGovern, Sean wrote:

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

Absolutely, Sean. The problem is that some tools (like Jasper) don't have the facility to create a view and then execute a select. They only have room for a single SQL statement. Or at least I've never gotten multiple statements to work.

My other issue with ad hoc VIEWS is really a personal quibble: the old system admin brain cells in my head don't like creating ad hoc views and just leaving them out there unless they're going to be used frequently. I might add a DROP VIEW to your statements above, especially if the "complex criteria" portion created views using user-entered parameters. Then again, by putting the VIEW in QTEMP, it will go away when the job ends, so the point may be moot, unless this is a server job.

Joe