Re: Can I do this in SQL? How? - Found the problem

[Luis Rodriguez <luisro58@xxxxxxxxx>]

Birgitta,

Thanks for your email. This one will go into the archives (filed under
"Amazing (SQL) Stories")

regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Fri, Feb 12, 2010 at 1:08 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>wrote:

> ... it seems I found the problem (why I could not insert a row using a
> CTE).
>
> ... If someone is interested what happened:
> Normally I work in an environment where commitment control is used.
> For testing I created a table in the QTEMP (which was not journaled).
> To insert/update or delete records in this table I either need to change my
> environment to work without commitment control or to add WITH NC to my
> Insert/Update/Delete statement. In this way my SQL-Statement had 2
> WITH-Clauses, which was denied, but the syntax checker complained about the
> 1st WITH which was my 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:58
> An: Midrange Systems Technical Discussion
> Betreff: Re: Can I do this in SQL? How?
>
> Birgitta,
>
> This works:
> -----------------
> 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'
> -----------------
>
> Dropped the '(' before the WITH and the last ')'
>
> Regards,
>
> Luis Rodriguez
> IBM Certified Systems Expert — eServer i5 iSeries
>
>
> On Wed, Feb 10, 2010 at 9:17 AM, Luis Rodriguez <luisro58@xxxxxxxxx>
> wrote:
>
> > Birgitta,
> >
> > Ran your example with the *SYN (Syntax checker) option of SQL, without
> > problems. In fact, the V6R1 SQL Reference states that
> >
> > <QUOTE/>
> >
> > The
> > *table name *of a common table expression can only be referenced in the *
> > select-statement*, INSERT statement, or CREATE VIEW statement that
> defines
> > it.
> >
> > </QUOTE>
> > A PTF needed, perhaps?
> >
> >
> > Regards,
> > Luis Rodriguez
> > IBM Certified Systems Expert — eServer i5 iSeries
> >
> >
> >   On Wed, Feb 10, 2010 at 9:03 AM, Luis Rodriguez
> <luisro58@xxxxxxxxx>wrote:
> > > Birgitta,
> > >
> > > I ran my example with the *STD (ANSI) option enabled, and it ran without
> a
> > > problem.
> > >
> > > Regards,
> > >
> > > Luis Rodriguez
> > > IBM Certified Systems Expert — eServer i5 iSeries
> > >
> > >
> > >   On Wed, Feb 10, 2010 at 8:56 AM, Birgitta Hauser <
> > > Hauser@xxxxxxxxxxxxxxx> wrote:
> > >
> > > > 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.
> --
> 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.