AW: Can I do this in SQL? How?

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

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

It is because a CTE is part of an SELECT-Statement and not of any other
statement!
But it is even not possible to use a CTE within a (Sub-)SELECT within a
UPDATE or INSERT-Statement.
According to the documentation only a full select is allowed.

Just for clarification:
1. A Sub-Select can consist Of SELECT, FROM, WHERE, GROUP BY, HAVING. Within
a Sub-Select other Sub-Selects can be nested.
2. A Full-Select can combine several Sub-Selects by using UNION, INTERSECT,
EXCEPT and an ORDER BY CLAUSE
3. A Select Statement is a Full-Select where CTEs can be added.

Example:
Assumed we have the following table
Create Table qtemp/MySales
as (Select Year(SalesDate) SalesYear, CustNo, Sum(Amount) Total
      From Sales
      Group By Year(SalesDate), CustNo)
With No Data;;

Now we want to insert data:
1. Sub-Select Only --> Works!!!
Insert into MySales
      (Select Year(SalesDate), CustNo, Sum(Amount)
          From Sales
          Where CustNo = '10001'
          Group By Year(SalesDate), CustNo);

2. Nested Sub-Select --> Works!!!
Insert Into MySales
      (Select * 
          From (Select Year(SalesDate) SalesYear, CustNo, Sum(Amount) Total
                   From Sales
                   Group By Year(SalesDate), CustNo) x
          Where CustNo =  '10001');

3. Common Table Expression --> NOT Allowed!!! (not even in Release 6.1)
Insert Into MySales
      (With x as (Select Year(SalesDate) SalesYear, CustNo, Sum(Amount)
Total
                    From Sales
                  Group By Year(SalesDate), CustNo)
       Select *
          From x
          Where CustNo = '10001')
With NC
;;

Same situation for updates.
It is the way IBM implemented it. 

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Joe Pluta
Gesendet: Wednesday, 10. February 2010 00:54
An: Midrange Systems Technical Discussion
Betreff: 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