Re: SQL row processing order for UDF

Dennis,

It's silly to pass MTD, QTD, YTD....if there's concern about the UNIZ
box being able to add properly...perhaps they shouldn't be moving! :)

But you can do this with only SQL, but the statement would be ugly and
basically you'd be making lot's of passes though the data.

Instead, since you have do this many times consider writing a program that takes
- an SQL statement
- the field(s) to sum for MTD, QTD, YTD
- (optionally) flags to indicate that MTD, QTD, YTD are desired.

Using the SQLDA, you can find out the fields being returned, and
output the data as needed.

HTH,
Charles

On Wed, Jun 16, 2010 at 8:46 PM, Dennis Lovelady <iseries@xxxxxxxxxxxx> wrote:

Yeah.  Thanks, Simon.  :)  Back when I started this thread, I said I might
end up doing it in a program.  But then it gained its own life and my
curiosity got the better of me.  Although SQL does a pretty good job of
figuring out the right (or at least a good) approach to presenting data, I
have difficulty understanding why any product is designed so that it cannot
be directed -absolutely- in some predictable way.  Oh well... there are
better battles to be fought!

The real problem here is that there's a migration going on from a product on
iSeries to a different product on Unix.  There will be many iterations of
data mapping, and the PeopleSoft team want the mapped data to contain MTD,
QTD, and YTD values.  To me, it makes sense to build those columns when
building everything else, and that's what I was about.  There are many of
these to build, and each is just unique enough to require its own
independent solution.  To develop programs for this, with our change
management practice in place, is an undertaking of significant proportions
for our small team of two who also provide all support and enhancements for
the applications on the i.  So I guess we'll provide zeros in the QTD and
YTD fields (the data are to be summarized at MTD level anyway) and let them
worry about the roll-up.  A shame - and I know they're going to wind up
requiring that we validate the roll-up.  (Rolls eyes.)

Thanks, all, for your advice and counsel on this.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"He is so unlucky that he runs into accidents which started out to happen to
somebody else."
       -- Don Marquis

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

I was going to reply to one of your earlier appends but I thought the
answer was implicit in some of the responses you received. Anyway,
your statement is correct. There is no way to tell SQL to process
input records in a particular sequence. You can control the sequence
of the output records via ORDER BY but SQL reserves the right to
decide how best to process the input. You can play silly buggers with
syntax and perhaps get the desired result but that is more a side-
effect and therefore may change with different releases, PTFs, or the
current state of main store.

Since you seem to need the data in a particular sequence you could
select the desired records and insert them into a temporary file. Use
ORDER BY to ensure the sequence. Then process the temporary file such
that a table scan is performed. I would expect simple RRN sequence to
be applied in that case. Apply your running-total function to the
query over the temporary file. The problem is that SQL could still
decide to do things differently (e.g., build an index and process via
that in some sequence it has determined is "better").

Or just do it without SQL i.e., native I/O. There are some things for
which SQL is still unsuited.


Regards,
Simon Coulter.
--------------------------------------------------------------------
    FlyByNight Software         OS/400, i5/OS Technical Specialists

    http://www.flybynight.com.au/
    Phone: +61 2 6657 8251   Mobile: +61 0411 091 400        /"\
    Fax:   +61 2 6657 8251                                   \ /
                                                              X
                  ASCII Ribbon campaign against HTML E-Mail  / \
--------------------------------------------------------------------



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