SQL III-8

• ? Using the CASE conditional expressions
• ? Converting a data item from one data type to another
• ? Saving data entry time by using row value expressions

SQL has a CASE statement and a CASE expression. A CASE expression is only part of a statement — not a statement in its own right. In SQL, you can place a CASE expression almost anywhere a value is legal. At run time, a CASE expression evaluates to a value. SQL’s CASE statement doesn’t evaluate to a value; rather, it executes a block of statements.

You can use the CASE expression in the following two ways:

• ? Use the expression with search conditions. CASE searches for rows in a table where specified conditions are True. If CASE finds a search condition to be True for a table row, the statement containing the CASE expression
• makes a specified change to that row.

? Use the expression to compare a table field to a specified value. The outcome of the statement containing the CASE expression depends on which of several specified values in the table field is equal to each table row.

• WHEN condition1 THEN result1
• WHEN condition2 THEN result2
• ...
• WHEN conditionn THEN resultn
• ELSE resultx
• END

• UPDATE FLIGHT
• SET RefuelStop = CASE
• WHEN RefuelStop = ‘San Jose’
• THEN NULL
• ELSE RefuelStop
• END ;

• UPDATE FLIGHT
• SET RefuelStop = NULLIF(RefuelStop, ‘San Jose’) ;