Computing the product of a column groupped by...

[Michel de Becdelièvre]

I need your help here :

I need to compute the product of a column, with conditions, over a
table, grouped by a key (it is a probability table).

In fact I need the exact equivalent of :

select key1, key2, sum( val )
from "sometable"
where "some parameterized condition"
group by key1, key2

*except* that I want the *multiplication* of all "val" instead of the sum.

I'd like the output to behave as much as a table column aggregative
function as possible, it is used in other queries.

In Oracle I'd solve this by using pipelined table functions, in DB2 I'm
lost, since table functions can only return the result of a select.

Can anyone help me ?

Am I stuck with using a procedure storing large results sets in a
temporary table with all the concurrency problems that this causes ?

Using DB2 for windows / UNIX 8.1.2.

Thanks in advance for any suggestion.

[Tonkuma]

My two ideas.
1) EXP(SUM(LOG(val)))
But, this returns floating point number. So, exact value could not be
received.

2) Use recursive query. (and common table expression, if you want
combine with other data)

[Michel de Becdelièvre]

"Tonkuma" <to*****@jp.ibm.com> a écrit dans le message de news:
11**********************@g10g2000cwb.googlegroups. com...

My two ideas.
1) EXP(SUM(LOG(val)))
But, this returns floating point number. So, exact value could not be
received.

Stupid me, I should have thought of this.

I like this idea, I'll check if there is not too much imprecision in the
result, I think it should be OK. It's probabilities anyway.

thanks !

[Knut Stolze]

Tonkuma wrote:

My two ideas.
1) EXP(SUM(LOG(val)))
But, this returns floating point number. So, exact value could not be
received.

2) Use recursive query. (and common table expression, if you want
combine with other data)

Another alternative is to roll your own aggregates. But this is not
trivial: http://tinyurl.com/7z49y and for groupings:
http://tinyurl.com/b2rq5

--
Knut Stolze
DB2 Information Integration Development
IBM Germany