× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.