How to Implement a Database in MySQL for HTML5 and CSS3 Programming
How to Use SQL with Microsoft Access
How to Calculate Date Values in SQL Data for HTML5and CSS3 Programming

How to Use the SQL CASE Expression with Search Conditions

One powerful way to use the SQL CASE expression is to search a table for rows in which a specified search condition is True. If you use CASE this way, the expression uses the following syntax:

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

CASE examines the first qualifying row (the first row that meets the conditions of the enclosing WHERE clause, if any) to see whether condition1 is True. If it is, the CASE expression receives a value of result1.

If condition1 is not True, CASE evaluates the row for condition2. If condition2 is True, the CASE expression receives the value of result2, and so on. If none of the stated conditions are True, the CASE expression receives the value of resultx.

The ELSE clause is optional. If the expression has no ELSE clause and none of the specified conditions are True, the expression receives a null value. After the SQL statement containing the CASE expression applies itself to the first qualifying row in a table and takes the appropriate action, it processes the next row. This sequence continues until the SQL statement finishes processing the entire table.

Update values based on a condition

Because you can embed a CASE expression within an SQL statement almost anywhere a value is possible, this expression gives you tremendous flexibility. You can use CASE within an UPDATE statement, for example, to make changes to table values — based on certain conditions. Consider the following example:

UPDATE FOODS
 SET RATING = CASE
     WHEN FAT < 1
      THEN 'very low fat'
     WHEN FAT < 5
      THEN 'low fat'
     WHEN FAT < 20
      THEN 'moderate fat'
     WHEN FAT < 50
      THEN 'high fat'
     ELSE 'heart attack city'
    END ;

This statement evaluates the WHEN conditions in order until the first True value is returned, after which the statement ignores the rest of the conditions.

A database table that shows the fat content of 100 grams of certain foods can contain a RATING column that gives a quick assessment of the fat content’s meaning. If you run the preceding UPDATE on the FOODS table, the statement assigns asparagus a value of very low fat, gives chicken a value of low fat, and puts roasted almonds in the heart attack city category.

Avoid conditions that cause errors

Another valuable use of CASE is exception avoidance — checking for conditions that cause errors.

Consider a case that determines compensation for salespeople. Companies that compensate their salespeople by straight commission often pay their new employees by giving them a draw against the future commissions they’re expected to earn. In the following example, new salespeople receive a draw against commission; the draw is phased out gradually as their commissions rise:

UPDATE SALES_COMP
 SET COMP = COMMISSION + CASE
        WHEN COMMISSION > DRAW
         THEN 0
        WHEN COMMISSION < DRAW
         THEN DRAW
       END ;

If the salesperson’s commission is zero, the structure in this example avoids a division-by-zero operation, which would cause an error if allowed to happen. If the salesperson has a nonzero commission, the total compensation is the commission plus a draw that’s reduced in proportion to the size of the commission.

All of the THEN expressions in a CASE expression must be of the same type — all numeric, all character, or all date. The result of the CASE expression is also of the same type.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
How to Run a Script with phpMyAdmin in SQL for HTML5and CSS3 Programming
How to Add SQL Data One Row at a Time
Microsoft SQL Server 2008 All-in-One For Dummies Cheat Sheet
How to Use Keys to Access Information Quickly in a SQL Database
How to Retrieve SQL Data with PHP for HTML5 and CSS3 Programming
Advertisement

Inside Dummies.com