Question Re: list() in a case statement in Technology Q&A

list() in a case statement

Former Member — Thu, 24 Feb 2011 15:49:34 GMT

Wondering if anyone can shed some light on the possibilies of having a list() in a case statement. ex:

select (case when [field] = [x] then list([y], [z]) else [a] end) from [table]

couldn't find any documentation and the list() function seems a bit limited from the documents info.

Appreciate your help. I'm using sql anywhere 11.

Re: list() in a case statement

VolkerBarth — Thu, 24 Feb 2011 16:55:58 GMT

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.

Re: list() in a case statement

Former Member — Thu, 24 Feb 2011 17:48:48 GMT

Volker, great stuff with the ||. That did the trick. And, I appreciate the notes on LIST. Helped tremendously. Thanks again