First I should point out that the "case expression" only arrived in Ingres II with release 2.5. So you may not have access to it yet. But it would indeed fulfill the need you describe here. It is used in select lists or where clauses whenever a scalar expression can be used. There are 2 flavours of the syntax - simple and searched - and they look like this:
simple case -
case <case operand> when <value1> then <result1> when <value2> then <result2> ... when <valuen> then <resultn> else <resultx> end
searched case -
case when <search expr1> then <result1> when <search expr2> then <result2> ... when <search exprn> then <resultn> else <resultx> end
All of <case operand>, the <valuen>'s, the <resultn>'s are expressions that can be as simple as column references or as complex as arithmetic or string expressions or even another nested case expression. The <search exprn>s are Boolean expressions. In both forms of case, the else clause is optional (the result is null if there are no matching when clauses and there is no else clause).
An obvious example of simple case is:
"case state_code when 'CA' then 'California' when 'NY' then 'New York' when 'TX' then 'Texas' ... end"
The searched case version of the same expression is:
"case when state_code = 'CA' then 'California' when state_code = 'NY' then 'New York' ... end"
One final note: as you can see by my example, Ingres simply does a linear search through the when clauses until it finds a match. So for large case expressions, it is best (if possible) to order the when's according to the anticipated distribution of code values.
Sent: Wednesday, December 12, 2001 7:01 PM
Subject: How to use the "CASE" expression in SQL
I _think_ the "CASE" expression would allow me, for example, to replace a
code held in a column with something more meaningful especially in a
SQL-based adhoc report/query for users.
If that assumption is correct, how do you use it? I've tried a number of
permutations and can't suss it.