Many thanks Helmut,

That did the trick, problem solved.

Cheers, Fin.

Background:
Whenever a new built-in function is added to DB2 it gets a version-stamp.
This is to avoid overriding a pre-existing user defined function of the
same name which may be an equally good match.
By running db2upd you move the version stamp of the DB forward which
makes the new function visible.

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

Why LISTAGG doesn't support distinct values ex. listagg(distinct
COLUMN,',')? Such statement executes but doesn't work. Maybe in future
release?

On 5/13/2011 4:52 AM, AD wrote:
support (off hand I don't see why not), I'll fix it, otherwise I'll need
to block it properly.
Ignoring the keyword is definitely wrong *eggontheface*.

Refer support to me directly.

Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

On May 13, 10:49*pm, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:
A workaround may be LAG and NULLIF, like...

------------------------------ Commands Entered
------------------------------
SELECT workdept
, COUNT(*) AS members
, SUBSTR(
LISTAGG( VARCHAR( NULLIF(edlevel , lag_edlevel) ) , ' ' )
WITHIN GROUP(ORDER BY edlevel)
, 1 , 20 ) AS distinct_edlevel
FROM (SELECT e.*
, LAG(edlevel)
OVER(PARTITION BY workdept
ORDER BY edlevel) AS lag_edlevel
FROM employee e
) e
GROUP BY
workdept
;
------------------------------------------------------------------------------

WORKDEPT MEMBERS DISTINCT_EDLEVEL
-------- ----------- --------------------
A00 3 14 18 19
B01 1 18
C01 3 16 18 20
D11 9 16 17 18
D21 6 14 15 16 17
E01 1 16
E11 5 12 14 16 17
E21 4 14 16

8 record(s) selected.

I'm quite new to DB2 and using Express-C only so I don't know how to
open PMR (which seems to be available to customers only).

On 5/13/2011 4:08 PM, AD wrote:

--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau