SQL CASE statements on Informix - Can you set more than one field in the END section of a case block?

Question

Using IBM Informix Dynamic Server Version 10.00.FC9

I'm looking to set multiple field values with one CASE block. Is this possible? Do I have to re-evaluate the same conditions for each field set?

I was thinking of something along these lines:

SELECT CASE WHEN p.id = 9238 THEN ('string',3) END (varchar_field, int_field);

Where the THEN section would define an 'array' of fields similar to the syntax of

INSERT INTO table (field1,field2) values (value1,value2)

Also, can it be done with a CASE block of an UPDATE statement?

UPDATE TABLE SET (field1,field2) = CASE WHEN p.id=9238 THEN (value1,value2) END;

Answer 1

Normally, I'd ask for the version of Informix that you're using, but it probably doesn't matter much this time. The simple answer is 'No'.

A more complex answer might discuss using a row type constructor, but that probably isn't what you want on the output. And, given the foregoing, then the UPDATE isn't going to work (and would require an extra level of parentheses if it was going to).

Answer 2

No, a CASE statement resolves to an expression (see IBM Informix Guide to SQL: Syntax CASE Expressions) and can be used in places where an expression is permitted. An expression is a single value.

from http://en.wikipedia.org/wiki/Expression_%28programming%29

An expression in a programming language is a combination of explicit values, constants, variables, operators, and functions that are interpreted according to the particular rules of precedence and of association for a particular programming language, which computes and then produces (returns, in a stateful environment) another value.