The Language of SQL- P16:Research has shown that, being pressed for time, most readers tend to skip the introduction of any book they happen to read and then proceed immediately to the first real chapter. With that fact firmly in mind, we will only cover relatively unimportant material in the introduction, such as an explanation of what you will and will not learn by reading this book. | The Searched Format 61 This next example will be taken from this data ProductID Fruit Vegetable Spice ProductDescription 1 X Apple 2 X Orange 3 X Mustard 4 X Carrot In this situation the database contains multiple columns to indicate whether the product is a fruit vegetable or spice. A CASE expression to create the same output for this data is SELECT CASE WHEN Fruit X THEN Fruit WHEN Vegetable X THEN Vegetable ELSE Other END AS Category ProductDescription AS Description FROM Products Once again the result is Category Description Fruit Apple Fruit Orange Other Mustard Vegetable Carrot Since the data now uses three separate columns to indicate if the product is a fruit vegetable or spice you need to use the searched format of the CASE clause in order to apply the needed logic. The simple format only works with an analysis of a single column. Due to the inherent complexity of IF-THEN-ELSE logic the CASE expression is one of the more challenging topics in this book. In this chapter we have focused on using CASE expressions in the SELECT columnlist. However CASE expressions can also be utilized in other SQL clauses such as the ORDER BY clause and other clauses not yet discussed such as the WHERE and HAVING clauses. 62 Chapter 6 Column-Based Logic Let s give just one example of additional uses of the CASE expression. Although we have not yet talked about the WHERE clause let s imagine that we know something about it. As will be explained in Chapter 7 the WHERE clause allows you to apply selection criteria to the rows that will be presented to the user. A typical expression might be something like WHERE ProductDescription White Glove This is a very specific directive. You only want to see rows where the product is a white glove. The value of the CASE expression is that it allows you to apply conditional logic to the value you re looking for perhaps based on the value of some other column. For example you may have another column named ProductType which gives more .
