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;

Solution 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).

Solution 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.

Comments

[CheeseConQueso]

I had more than a feeling that you would have the answer for this. I also was going to list the version of Informix (you got me into that habit after this question I promise) but I always forget how to get it.

[CheeseConQueso]

Can't a "single" produced value be an array? My curiosity is fed from the syntax of, for example, Perl, where you can return an array produced by a function into a single reference/variable (my @array = function($var1,$var2);)

[KM.]

SQL doesn't have "arrays", it has the basic types: publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topi‌​c=/… which you can use to build tables and result sets

[CheeseConQueso]

poor sql... well i just have a mismatched lexicon - when i see stuff like where in (1,2,3) i think array, but it is a set right? thanks for the clarifications

[Jonathan Leffler]

@CheeseConQueso: pick your program - say, onstat: onstat -V or onstat -version.