Re: Can I do this in SQL? How?

Strange!
I run the examples the examples I posted in my eMail this morning on a 6.1
System.
And could not execute the example with the CTE.

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 Luis Rodriguez
Gesendet: Wednesday, 10. February 2010 14:15
An: Midrange Systems Technical Discussion
Betreff: Re: Can I do this in SQL? How?

Birgitta,

As always, your comments are very knowledgeable and helpful for us mere SQL
mortals (and I say that with a LOT of respect) but, in our V5R3 system this
is allowed
:
----------------------
INSERT INTO QTEMP/TEST2
with t1 as (Select c1, c2 from qtemp/test1)
Select concat('A', C1) as c3,
concat('B', C2) as c4
From T1
----------------------

Would not this qualify as an INSERT from a CTE?

Best Regards,,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Wed, Feb 10, 2010 at 1:28 AM, Birgitta Hauser
<Hauser@xxxxxxxxxxxxxxx>wrote:

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.