Re: Std Dev in RPG

Chuck,

Thanks for the expertise and advice. I left out some info for the sake of
brevity...sorry.

I'm at v5r2...Al Barsa used to howl each time I'd tell him that.

The data for the array comes from a table. After performing the required
functions, I throw out the oldest record, add a new one, and start the
process all over again - a "moving average" for example. I could change
the beginning and ending dates and use SQL to perform the functions. I
wonder about the efficiency of that versus reading one record at a time,
updating the array, and performing the calculations on the array. Also,
giving SQL a beginning and ending date is problematic, as I don't consider
weekends or holidays and would have to account for that somehow.

For this specific application, the number of days considered would be 10.

TIA.


James R. Newman, CDP



On Sat, Mar 24, 2012 at 5:40 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

On 24-Mar-2012 11:27 , James Newman, CDP wrote:

So I have 10 items in an array and I'd like to figure out a mean
(average) and standard deviation. For the mean, I can XFOOT, then
divide by the number of elements in the array. I've only used SQL
with tables - can I use SQL against an array?

The SQL may not be used as easily as one might desire with arrays,
for lack of both an ARRAY data type or TABLE(:array_name) capability,
but there are ways. Easier with IBM i 7.1 SQL I presume, with an ARRAY
data type and associated functions [using a User Defined Type (UDT)
however], but I have neither experience with the support nor read much
about the v7r1 array type support; perhaps moot, as the OS release was
left undisclosed, for what might be available to the OP.

Per "10 items", does that imply there will always be a specific
and\or limited number of items below a specific count? What is the
origin and storage location for the data values which eventually become
elements of an array declared in the program? Irrespective of the
answers to those questions, whatever loads the array could instead
provide the data as row data via an external user defined TABLE function.

A limited number of items could be elements of a values-clause [or
similarly selected as UNION set of data], a derived result TABLE,
against which the STDDEV, AVG, and COUNT aggregate functions could operate.

An example using dynamic SQL that works at v5r3 for four values;
though a values row expression, since v5r4, is much prettier than the
UNION ALL:

with SD (d8) as
( select cast(? as dec(8)) d8 from qsqptabl
union all select cast(? as dec(8)) d8 from qsqptabl
union all select cast(? as dec(8)) d8 from qsqptabl
union all select cast(? as dec(8)) d8 from qsqptabl
)
select int(avg(d8))
, dec(stddev(d8), 10, 2)
, int(count(*))
from SD
;
open abovePreparedStmt using :e1, :e2, :e3, :e4
; -- :HV e1 to e4 are array[1] to array[4]

Regards, Chuck
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.