|
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 Imight
end up doing it in a program. But then it gained its own life and myof
curiosity got the better of me. Although SQL does a pretty good job
figuring out the right (or at least a good) approach to presentingdata, I
have difficulty understanding why any product is designed so that itcannot
be directed -absolutely- in some predictable way. Oh well... thereare
better battles to be fought!product on
The real problem here is that there's a migration going on from a
iSeries to a different product on Unix. There will be manyiterations of
data mapping, and the PeopleSoft team want the mapped data to containMTD,
QTD, and YTD values. To me, it makes sense to build those columnswhen
building everything else, and that's what I was about. There aremany of
these to build, and each is just unique enough to require its ownproportions
independent solution. To develop programs for this, with our change
management practice in place, is an undertaking of significant
for our small team of two who also provide all support andenhancements for
the applications on the i. So I guess we'll provide zeros in the QTDand
YTD fields (the data are to be summarized at MTD level anyway) andlet them
worry about the roll-up. A shame - and I know they're going to windup
requiring that we validate the roll-up. (Rolls eyes.)happen to
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
somebody else."be
-- Don Marquis
Is there really no way to tell SQL the order in which rows are to
theprocessed?
I was going to reply to one of your earlier appends but I thought
withanswer 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
thesyntax and perhaps get the desired result but that is more a side-
effect and therefore may change with different releases, PTFs, or
Usecurrent 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.
suchORDER BY to ensure the sequence. Then process the temporary file
tothat a table scan is performed. I would expect simple RRN sequence
viabe 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
forthat in some sequence it has determined is "better").
Or just do it without SQL i.e., native I/O. There are some things
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.