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.

asked 24 Feb '11, 15:49

zippidydo's gravatar image

zippidydo
377151521
accept rate: 0%

edited 21 Mar '14, 11:10

Mark%20Culp's gravatar image

Mark Culp
23.3k9132275

Comment Text Removed

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.

permanent link

answered 24 Feb '11, 16:55

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 33%

edited 24 Feb '11, 17:06

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

(24 Feb '11, 17:48) zippidydo
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×12
×4

question asked: 24 Feb '11, 15:49

question was seen: 1,131 times

last updated: 21 Mar '14, 11:10