LIST does work in a CASE expression. The following is a (not very useful) sample:
select case
when creator <> 3
then 'System procs: ' || list(proc_name, ', ' order by proc_name)
else 'My procs: ' || list(proc_name, ', ' order by proc_name)
end case as MyProcList
from sysprocedure group by creator
Note, however, that LIST is an aggregate function, i.e. the [field] you refer to in the CASE WHEN expression must be part of the GROUP BY clause.
That's the reason I used "GROUP BY creator".
As to your notation of (list[y], [z]): Note that LIST is not a string concatenation, instead it lists all particular values per group. A simple string concatenation can be done with operator || (as in the sample) or the STRING function.
answered
24 Feb '11, 16:55
Volker Barth
39.5k●355●539●810
accept rate:
34%