×
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.
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
midrange.com
