Re: SQL row processing order for UDF

Dennis,

:)

If it were me, rather than parsing the SQL looking for QTD()....

I'd just pass in the QTD expression...

I don't see any benefit to parsing the SQL to find QTD(invamt +
taxamt) when you could simply pass in 'invamt + taxamt' as the QTD
parameter

Unless the location of the QTD column in the output is variable?

Charles

On Thu, Jun 17, 2010 at 9:34 AM, Dennis Lovelady <iseries@xxxxxxxxxxxx> wrote:

Thanks, Charles!

We've reached the conclusion that the unix system adds OK, but we're just
not sure if it subtracts correctly.  Well, technically we know that Unix is
OK; it's the application we're concerned about.  :)

I'm already into this approach (great minds, same bat-channel), and the most
significant complexity that I've discovered so far is that there can be
(often are) more than one column upon which to perform the roll-up.  (I had
solved that in the UDF by specifying an extra parameter that was to be used
as an identifier.)

So what I'm looking into, is pre-parsing the SQL I had come up with before
(complete with QTD, YTD specs), replace QTD(...) etc with zero in the SQL,
and replace the zeros with the calculation in RPG.  Should be pretty slick
when it's done.  :)

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
CONGRESS.SYS corrupted.  Reboot Washington DC <Y/N>?

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.