VistaDB 5
CASE
CASE

Think of this as the IF...THEN statement for SQL. Most languages have a way to determine if some condition has occurred then return this value. The CASE function is the way to do that in SQL.

The CASE function is used to vary a result expression based upon one or more conditions. ELSE is also a part of the CASE condition and is used as the logical NOT of the selected case.

CASE has two formats for working with data:

  • Simple case of an expression comparison to a set of expressions to determine the result.

Evaluation of boolean expressions to determine the result.

CASE should not be used in a WHERE clause

See the complete article on how to rewrite queries that have a case in a where clause.



Example

Example Schema

Column Name Column Type
AppType Char(1)
Name VarChar(64)
AppCost Int

Example Data

AppType Name AppCost
A SomeApp.exe 40
S WatchThis.scr 125
Z Setup.zip 200
D VistaDB.DLL 750
Q Unknown.pdb 0

The SQL statement will replace the single letter 'A' with 'Application' in the result set.

Exact Matching Example
Copy Code
SELECT Name, CASE AppType
      WHEN 'A' THEN 'Application'
      WHEN 'S' THEN 'Screen Saver'
      WHEN 'D' THEN 'DLL'
      WHEN 'Z' THEN 'ZIP'
      ELSE 'Unknown Type'
   END
FROM AppTable 

This SQL example will return a string from the AppCost rather than the exact number. 

Evaluated CASE Expressions
Copy Code
SELECT Name, CASE
      WHEN AppCost = 0 THEN 'Free'
      WHEN AppCost < 50 THEN 'Under $50'
      WHEN AppCost >= 50 and AppCost < 250 THEN 'Under $250'
      WHEN AppCost >= 250 and AppCost < 1000 THEN 'Under $1000'
      ELSE 'Special Pricing'
   END AS PRICETYPECOL
FROM AppTable
See Also