Week 3

Day 20
Advanced Topics: Single-Table Queries

Well, you’re in the home stretch now. You’ve learned the fundamentals, and you’ve worked through many of the issues concerned with writing triggers and stored procedures. These last two chapters will help you understand some of the advanced issues in T-SQL programming.

In this first look at advanced topics in T-SQL, you will look at single-table topics. Although all of these topics are also relevant to multitable queries, they are not specific to joins. Tomorrow, the second part of the advanced topics section relates to joins and multitable queries.

I’ll be describing typical programming problems in this chapter and useful workarounds. Take the time to understand each problem completely before you look at the solution. That way, you can put this solution to use when you encounter a similar problem in the future.

Here are the issues I cover today:

•  The case operator

•  Optimistic locking

•  Understanding worktables

The case Operator

First, forget everything you ever knew from any other language about the CASE statements. case in SQL is an operator, not a statement, so it is used as a part of a SELECT, INSERT, or UPDATE statement.

case returns one of several expressions based on the value of another expression. In its simplest form, accessing no tables, case expands the value of an expression, as in this example, which decides on the basis of the day of the week whether today is a weekend day or not:

select  “weekday” =
       CASE
         WHEN datepart(dw, getdate()) in (1, 7) THEN “FALSE”
       ELSE “TRUE”
      END

Learning to use case properly is not the problem. Learning to identify situations where case is the right solution is more difficult. I’ll show you one application of case here. I’ll demonstrate how to write a crosstab report in a single step with SELECT.

case with SELECT: Crosstabs

In this section, we’ll write a crosstab report using case. Then we’ll use a view to simplify execution of the crosstab.

A crosstab report is a matrixed result set that shows how values displayed in columns relate to values displayed in rows. What distinguishes a crosstab report from an ordinary result set is that actual column values are used in the column headings.

To understand any crosstab query, you need to start by sketching the result set you need. In this example, we’re going to analyze sales of types of books by store_id. We’ll display the store_id along the left and the book types across the top, as in Figure 20.1.

Figure 20.1.  A crosstab report.

What goes in each cell of the report? You can determine the total sales (in dollars or units or discounted dollars or discounted after-royalty dollars) for each store_id for each type of book. In this example, we’ll determine total dollar sales (price * qty).

The Solution

Now that we understand the problem, we’ll use the case operator to generate the query. The first step is to see how you would use case to build a single column, then we’ll show the whole query.

Let’s build business and non-business columns, which will report sales of business books. For each row, we need a method to determine the business sales and the non-business sales. Here’s an expression to determine what component of a sale was of type business:

CASE titles.type WHEN “business” THEN sales.qty ELSE 0 END

The case expression allows you to create a fictitious business sales column. Here’s a non-business column:

CASE titles.type WHEN “business” THEN 0 ELSE sales.qty END

Here’s a query that shows each row in the sales table and its business and non-business sales:

select
    t.type,
    s.qty,
   “business” = CASE t.type WHEN “business” THEN s.qty ELSE 0 END,
   “non-business” = CASE t.type WHEN “business” THEN 0 ELSE s.qty END
  from titles t inner join sales s
      on t.title_id = s.title_id

type            qty      business     non-business
------------   ------    -----------     -------------
business      5       5             0
business      10      10            0
business      25      25            0
business      35      35            0
business      15      15            0
mod_cook       10      0             10
mod_cook       25      0             25
mod_cook       15      0             15
popular_comp  30      0             30
popular_comp  50      0             50
psychology    20      0             20
psychology    3       0             3
psychology    75      0             75
psychology    10      0             10
psychology    20      0             20
psychology    25      0             25
psychology    15      0             15
psychology    25      0             25
trad_cook     40      0             40
trad_cook     20      0             20
trad_cook     20      0             20

This is a useful first step toward building the final report. Intermediate reports like this allow you to check your work before the data disappears into a summary value, where mistakes are much harder to find. You can demonstrate that the case operator is working properly by making certain that each sale appears only in the business or non-business column (not both).

---

Note:  Note that you don’t use a WHERE clause to identify business books. All book types will appear in the final report, so you can’t use WHERE to exclude rows from being processed.

---