jefftyzzer wrote:
> On Jul 9, 3:40 pm, "Dave Hughes" <d...@[EMAIL PROTECTED]
> wrote:
>> jefftyzzer wrote:
>> > Friends:
>>
>> > In v8 or v9 LUW, if I were to write (in Java or C) my own
user-defined
>> > aggregate function, would I be able to use it in an OLAP windowing
>> > clause as I could use, e.g., COUNT or SUM?
>>
>> > Example:
>>
>> > SELECT
>> > JEFFS_UDAF() OVER(PARTITION BY SOME_COL)
>> > FROM
>> > SOME_TABLE;
>>
>> There's no such thing as a user-defined aggregate function in DB2*,
>> external or otherwise, so no you couldn't use it with OLAP windowing
>> because it couldn't exist ;-)
>>
>> Cheers,
>>
>> Dave.
>>
>> * Officially at least ... see Knut's fascinating dW article on a
>> possible implementation at:
>>
>>
http://www.ibm.com/developerworks/db2/library/techarticle/0309stolze/030
>> 9stolze.html
>>
>> Given that this relies on an existing aggregate function (MAX), I
>> suspect it /might/ work with OLAP windowing (though this depends on the
>> order of execution).
>
> Hi, Dave:
>
> It was with knowledge of Knut's article that I asked the question. I
> suppose another way to rephrase it is: If DB2 sees any ranking/
> numbering/aggregation function other than the ones that come "out of
> the box" (e.g., one that's user-defined) before the "(OVER PARTITION
> BY...)" clause, will it throw an SQL0109N error?
What you can do is to provide the partitioning information as input
parameter to your UDF (the inner one if you use my aggregation approach)
and then let your UDF handle things internally.
However, you would now have an aggregate function that needs grouping
sup****t and has to return a result for each group. This makes things more
complicated, but could potentially be addressed as described here, for
example:
http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0404stolze/index.html
--
Knut Stolze
Data Warehousing for DB2 z/OS
IBM Germane Research & Development
|
