VistaDB 6
VistaDB / Developer's Guide / SQL Reference / Functions / System Functions / CASE
In This Topic
    In This Topic

    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:

    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 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 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'
    FROM AppTable 

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

    Evaluated CASE Expressions
    Copy Code
          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'
    FROM AppTable
    See Also