case with a View

The problem with using case to handle a crosstab is that it’s not exactly easy to write. You can’t expect inexperienced SQL users to dash off an accurate report based on case in five minutes. The good news is that you can hide most of the complexity by using a view.

As you learned last week, a view is an object stored in a database that provides an alternative method of looking at data; it’s a stored SELECT statement that looks exactly like a table. Let’s create a view of the titles and sales data that incorporates the case logic you used to generate the crosstab report.

You could write a view to represent the entire report you just created, like this:

create        view       v_titles_sales_dollarsales
as
…  [the  entire  select     statement     above]

Later, to run the report you just ran, you would simply type:

select     *     from      v_titles_sales_dollarsales

You can provide better flexibility by storing an intermediate view and throw in some additional columns as well. The view will handle all of the case logic and provide direct access to columns representing sales by type. It would provide the basis for a wide variety of crosstab reports based on type. Here’s the view I would create:

create        view       v_titles_sales_by_type as
select
     t.pub_id,
      s.stor_id,
     s.ord_date,
      t.title_id,
     t.price,
     t.type,
     s.qty,
UNDECIDED    = CASE t.type WHEN ‘UNDECIED   ‘ THEN s.qty ELSE 0 END,
business     = CASE t.type WHEN ‘business    ‘ THEN s.qty ELSE 0 END,
mod_cook     = CASE t.type WHEN ‘mod_cook    ‘ THEN s.qty ELSE 0 END,
popular_comp = CASE t.type WHEN ‘popular_comp’ THEN s.qty ELSE 0 END,
psychology   = CASE t.type WHEN ‘psychology  ‘ THEN s.qty ELSE 0 END,
trad_cook    = CASE t.type WHEN ‘trad_cook   ‘ THEN s.qty ELSE 0 END
    from   titles  t  inner  join  sales  s
        on   t.title_id  =  s.title_id

Based on this view, you could create the dollar-sales analysis by store with much less code because most of the logic is stored in the view:

select
     stor_id,
     Total = sum(qty * price),
   UNDECIDED    = sum(UNDECIDED * price),
     business     = sum(business * price),
    mod_cook     = sum(mod_cook * price),
     popular_comp = sum(popular_comp * price),
     psychology   = sum(psychology * price),
    trad_cook    = sum(trad_cook * price)
      from   v_titles_sales_by_type
group  by  stor_id

The view handles all the complexity of the case expressions, leaving you free to write a pretty straightforward query. What’s more, the view lets you build a variety of crosstab reports relating type to other columns, and it also allows you to use other aggregate functions (min, max, avg) in your analysis. You can analyze units or dollars sold or join sales to discounts or roysched to determine profitability (this is much more complex). You can also do work on a specific date range, using the ord_date column in a WHERE clause.

Locking Solutions for Multiuser Editing

You’ve learned about the physical locking methods used by SQL Server to manage data integrity and consistency. Those methods are entirely automatic during SQL operations and transactions. Unfortunately, none of the automatic methods provide persistent locking while a user is viewing a row onscreen or making changes to that row. This is one of the most complex problems facing a SQL Server application programmer.

Let’s look at the details of data management in a multiuser update environment. We’ll look closely at the data synchronization issues between two users trying to access the same row of data for modification. We’ll consider five potential approaches to handling concurrency:

•  No locks

•  Persistent shared locks

•  Persistent exclusive locks

•  Manual check-out

•  Optimistic locking