How to use the "CASE" expression in SQL

How to use the "CASE" expression in SQL

Post by Inkster, Dougl » Tue, 18 Dec 2001 23:07:48



Hi Andrew,
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.

Doug.

-----Original Message-----

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.

Thanks
Andrew Scott

 
 
 

How to use the "CASE" expression in SQL

Post by Roy Ha » Fri, 04 Jan 2002 00:39:20



Quote:>Hi Andrew,
>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).

This item is possibly a little overripe now, but I just found it as I was
cleaning out my mailbox--and in any case I can't resist posting a small
correction to a Doug Inkster posting.  (OK, maybe it's not so much a correction
as a elaboration, but I am going to strut and swagger about it just the same.)

When a case expression is used in an update statement, and the column being
updated is non-nullable, then the else clause is actually mandatory.

Roy Hann
<http://www.rationalcommerce.com/>Rational Commerce Ltd.
"Ingres development, tuning, and training experts"

 
 
 

1. Using "Case" in SQL

The best place to start is the 'Transact-SQL Reference' online
documentation that comes with MSSQL. If you don't have the Books Online,
then check out some of the other publications referenced in this
newsgroup.  I'm sure you'll discover in time that having good reference
book(s) is a real joy!

Lynn

2. upgd sql7 to sql2000 - script error

3. using "case"

4. Oracle Resources (JOBS, RESUMES, LINKS, TUNING SCRIPT)!

5. Using "Select Case" in Procedures

6. SQL for finding source file names in an Application

7. HELP:- SQL "like" with wildcard expression

8. Need to convert data to CSV text format?

9. max of ("...","...","..")

10. SQL and "case"

11. Stupid question ("expression service")

12. "FROM" clause expression

13. Error "Full dBase expressions not allowed"