Introductory Transact-SQL (part 5)

By Addison Wesley

Converting Data Types

Converting data between types is equally simple. You can use either the CAST() or CONVERT() function to convert one data type to another, but CAST() is the SQL-92 P compliant method. Here's a SELECT that converts the Amount column in the orders table to a character string:

SELECT CAST(Amount AS varchar) FROM orders

--------
123.45
678.90
86753.09
678.90

Here's an example that illustrates how to convert a datetime value to a character string using a specific format:

SELECT CONVERT(char(8), GETDATE(),112)
--------
19690720

This example highlights one situation in which CONVERT() offers superior functionality to CAST(). CONVERT() supports a style parameter (the third argument above) that specifies the exact format to use when converting a datetime value to a character string. You can find the table of supported styles in the Books Online, but styles 102 and 112 are probably the most common.
CASE

In the examples throughout this book, you'll find copious use of the CASE function. CASE has two basic forms. In the simpler form, you specify result values for each member of a series of expressions that are compared to a determinant or key expression, like so:

SELECT CASE sex
WHEN 0 THEN 'Unknown'
WHEN 1 THEN 'Male'
WHEN 2 THEN 'Female'
ELSE 'Not applicable'
END

In the more complex form, known as a "searched" CASE, you specify individual result values for multiple, possibly distinct, logical expressions, like this:

SELECT CASE
WHEN DATEDIFF(dd,RentDueDate,GETDATE())>15 THEN Desposit
WHEN DATEDIFF(dd,RentDueDate,GETDATE())>5 THEN DailyPenalty*
DATEDIFF(dd,RentDueDate,GETDATE())
ELSE 0
END

A searched CASE is similar to an embedded IF...ELSE, with each WHEN performing the function of a new ELSE clause.

Personally, I've never liked the CASE syntax. I like the idea of a CASE function, but I find the syntax unwieldy. It behaves like a function in that it can be nested within other expressions, but syntactically, it looks more like a flow-control statement. In some languages, "CASE" is a flow-control keyword that's analogous to the C/C11 switch statement. In Transact-SQL, CASE is used similarly to an inline or "immediate" IF—it returns a value based on if-then-else logic. Frankly, I think it would make a lot more sense for the syntax to read something like this:

CASE(sex, 0, 'Unknown', 1, 'Male', 2, 'Female', 'Unknown')

or

CASE(DATEDIFF(dd,RentDueDate,GETDATE())>15, Deposit,
DATEDIFF(dd,RentDueDate,GETDATE())>5, DailyPenalty*
DATEDIFF(dd,RentDueDate,GETDATE()),0)

This is the way that the Oracle DECODE() function works. It's more compact and much easier to look at than the cumbersome ANSI CASE syntax.
Aggregate Columns

Aggregate columns consist of special functions that perform some calculation on a set of data. Examples of aggregates include the COUNT(), SUM(), AVG(), MIN(), STDDEV(), VAR(), and MAX() functions. They're best understood by example. Here's a command that returns the total number of customer records on file:

SELECT COUNT(*) FROM customers

Here's one that returns the dollar amount of the largest order on file:

SELECT MAX(Amount) FROM orders

And here's one that returns the total dollar amount of all orders:

SELECT SUM(Amount) FROM orders

Aggregate functions are often used in tandem with SELECT's GROUP BY clause (covered below) to produce grouped or partitioned aggregates. They can be employed in other uses as well (e.g., to "hide" normally invalid syntax), as the chapters on statistical computations illustrate.
Filtering Data

You use the SQL WHERE clause to qualify the data a SELECT statement returns. It can also be used to limit the rows affected by an UPDATE or DELETE statement. Here are some queries that use WHERE to filter the data they return:

SELECT UPPER(LastName), FirstName
FROM customers
WHERE State='TX'

FirstName
--- ---------
DOE John
DOE Jane

The following code restricts the customers returned to those whose address contains the word "Joshua."

SELECT LastName, FirstName, StreetAddress FROM customers
WHERE StreetAddress LIKE '%Joshua%'

LastName FirstName StreetAddress
-------- --------- ---------------
Doe John 123 Joshua Tree
Doe Jane 123 Joshua Tree

Note the use of "%" as a wildcard. The SQL wildcard % (percent sign) matches zero or more instances of any character, while _ (underscore) matches exactly one.

Here's a query that returns the orders exceeding $500:

SELECT OrderNumber, OrderDate, Amount
FROM orders
WHERE Amount > 500

OrderNumber OrderDate Amount
----------- ----------------------- --------
102 1992-02-27 00:00:00.000 678.90
103 1995-05-20 00:00:00.000 86753.09
104 1997-11-21 00:00:00.000 678.90

The following example uses the BETWEEN operator to return orders occurring between October 1990 and May 1995, inclusively. I've included the time with the second of the two dates because, without it, the time would default to midnight (SQL Server datetime columns always store both the date and time; an omitted time defaults to midnight), making the query noninclusive. Without specification of the time portion, the query would return only orders placed up through the first millisecond of May 31.

SELECT OrderNumber, OrderDate, Amount FROM orders
WHERE OrderDate BETWEEN '10/01/90' AND '05/31/95 23:59:59.999'

OrderNumber OrderDate Amount
----------- ----------------------- --------
101 1990-10-18 00:00:00.000 123.45
102 1992-02-27 00:00:00.000 678.90
103 1995-05-20 00:00:00.000 86753.09