RE: SQL row processing order for UDF

["Paul Nelson" <nelsonp@xxxxxxxxxxxxx>]

I've got nothing but good things to say about SEQUEL. I've been using it for
almost 20 years now.

I've even had situations where they've sent me enhancements that I needed
for various purposes, and then those are shipped as a version upgrade.

Paul Nelson
Office 512-392-2577
Cell 708-670-6978
nelsonp@xxxxxxxxxxxxx


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dennis Lovelady
Sent: Wednesday, June 16, 2010 10:52 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL row processing order for UDF

Well, looks like I'll be having crow for dinner.  Yum yum.  I've had it
before; I'll have it again.  :)

SEQUEL, it turns out, has a "DATABASE NAME" attribute - settable at the user
level - that determines whether it uses the classic (nonstandard) syntax or
the DB2 syntax.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"Health nuts are going to feel stupid someday, lying in hospitals dying of
nothing."
        -- Redd Foxx 


> 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.