RE: SQL row processing order for UDF

["Dennis Lovelady" <iseries@xxxxxxxxxxxx>]

Dang it, I'm back to square 1 on this.

This simple select:

with t1 as (
select dept, name 
from names                   
group by dept, name         
order by dept, name         
)                           
select dept, name from t1   

Produced these results at V5R3:

S&C     TEMPLETON 
EXEC    DALY       
IS      KING       
STAFF   NESSON     
OPER    PINA       
OPER    GYAMFI     
IS      GOLBA

Is there really no way to tell SQL the order in which rows are to be
processed?

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"I knew I was an unwanted baby when I saw that my bath toys were a toaster
and a radio."
        -- Joan Rivers 


> I used to think CASE tools were oh so cool.  Then RPG actually started
> getting improvements.  And the CASE tool vendors thought maintenance
> money
> was reserved for paying off leveraged buy outs instead of product
> improvements and they lagged behind, further and further, until they
> fell
> into disuse.
>
> Now I suppose SEQUEL is going that same route.
>
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:   "Dennis Lovelady" <iseries@xxxxxxxxxxxx>
> To:     "'Midrange Systems Technical Discussion'"
> <midrange-l@xxxxxxxxxxxx>
> Date:   06/16/2010 05:10 AM
> Subject:        RE: SQL row processing order for UDF
> Sent by:        midrange-l-bounces@xxxxxxxxxxxx
>
>
>
> Thanks to all who responded.  Rob's idea seems to fit the bill best in
> this
> case.
>
> Dennis Lovelady
> http://www.linkedin.com/in/dennislovelady
> --
> "Our vision is to speed up time, eventually eliminating it."
>         -- Alex Schure
>
> > Create a view with that statement.  Access the view with SEQUEL?
> >
> >
> > Rob Berendt
>
>
> > Dennis
> > I didn't, but that's a very good idea.
> >
> > However, it's not a complete solution since some uses of this,
> against
> > my
> > fervent complaints, will be implemented using the SEQUEL product,
> which
> > has
> > no support for the WITH statement (among many other things).   Don't
> > get
> > me
> > started.
> >
> > Any response to the "is it expected behavior" part?
> >
> > Dennis Lovelady
> > http://www.linkedin.com/in/dennislovelady
> --
> > Time is nature's way of keeping everything from happening at once.
> >
> >
> > > did you try to use an CTE?
> > >
> > > With x as (Select TRNADAT, Amount
> > >              From MyTable
> > >              Where ...
> > >              Order By TRNDAT)
> > > Select x.* MTD(Amount)
> > > > From x
> > > ;
> > >
> > > 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 Dennis
> > Lovelady
> > > Gesendet: Tuesday, 15. June 2010 15:30
> > > An: 'Midrange Systems Technical Discussion'
> > > Betreff: SQL row processing order for UDF
> > >
> > > I created a UDF like a more complex version of this:
> > >
> > >    CREATE FUNCTION MTD(date, decimal(11,2)
> > >
> > >    RETURNS DECIMAL(13,2)
> > >
> > >    RETURNS NULL ON NULL INPUT
> > >
> > >    .
> > >
> > > The select would like like:
> > >
> > >    SELECT TRNDATE, AMOUNT, MTD(AMOUNT)
> > >
> > >    ORDER BY TRNDATE
> > >
> > >
> > >
> > > And ideally the result would be a running total of month-to-date
> > values.
> > > Pretty slick.
> > >
> > >
> > >
> > > What I found, though, leads me to believe that the MTD function is
> > > being
> > > called before the ORDER BY, and that's giving me wildly incorrect
> > > results,
> > > even when the source table is already in that sequence.  When the
> > > source
> > > table is already in the correct sequence and I drop the ORDER BY, I
> > > *seem*to* get the results that I want, but I doubt there's any
> > > guarantee of
> > > that; besides, that's a requirement that'll be hard to meet.  So my
> > > two-part
> > > question is:  Is this expected behavior?   Is there a way around
> > this?
> > > Dennis E. Lovelady
> > > AIM/Skype: delovelady      MSN: fastcounter@xxxxxxxxxxxx
> > >  <http://www.linkedin.com/in/dennislovelady>
> > > www.linkedin.com/in/dennislovelady --
> > > "A fanatic is one who can't change his mind and won't change the
> > > subject."
> > >        - Winston Churchill
> > >
> > >
> > >
> > > --
> > > 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.