Subqueries and Query Expressions - The CAST Expression (SQL2)
(Page 8 of 12 )
The SQL standard has fairly restrictive rules about combining data of different types in expressions. It specifies that the DBMS shall automatically convert among very similar data types, such as 2-byte and 4-byte integers. However, if you try to compare numbers and character data, for example, the standard says that the DBMS should generate an error. The standard considers this an error condition even if the character string contains numeric data. You can, however, explicitly ask the DBMS to convert among data types using the CAST expression, whose syntax is shown in Figure 9-9.
Figure 9-9. SQL2 CAST expression syntax diagram
The CAST expression tends to be of little importance when you are typing SQL statements directly into an interactive SQL interface. However, it can be critical when using SQL from within a programming language whose data types don’t match the data types supported by the SQL standard. For example, the CAST expression in the SELECT clause of this query converts the values for REP_OFFICE (integers in the sample database) and HIRE_DATE (a date in the sample database) into character strings for the returned query results:
SELECT NAME, CAST REP_OFFICE AS VARCHAR, HIRE_DATE AS VARCHAR
FROM SALESREPS
The CAST expression can generally appear anywhere that a scalar-valued expression
can appear within a SQL statement. In this example, it’s used in the WHERE clause to convert a character string customer number into an integer, so that it can be compared with the data in the database:
SELECT PRODUCT, QTY, AMOUNT
FROM ORDERS
WHERE CUST = CAST '2107' AS INTEGER
Instead of specifying a data type in the CAST expression, you can specify a SQL2 domain. Domains are specific collections of legal data values that can be defined in the database under the SQL2 standard. They are fully described in Chapter 11 because of the role they play in SQL data integrity. Note that you can also generate a NULL value of the appropriate data type for use in SQL expressions using the CAST expression.
The most common uses for the CAST expression are:
- To convert data from within a database table where the column is defined with the wrong data type. For example, when a column is defined as a character string, but you know it actually contains numbers (that is, strings of digits) or dates (strings that can be interpreted as a month/day/year).
- To convert data from data types supported by the DBMS that are not supported by a host programming language. For example, most host programming languages do not have explicit date and time data types and require that date/time values be converted into character strings for handling by a program.
- To eliminate differences between data types in two different tables. For example, if an order date is stored in one table as DATE data, but a product availability date is stored in a different table as a character string, you can still compare the columns from the two tables by CASTing one of the columns into the data type of the other. Similarly, if you want to combine data from two different tables with a UNION operation, their columns must have identical data types. You can achieve this by CASTing the columns of one of the tables.
Figure 9-10. SQL2 CASE expression syntax diagram
The CASE Expression (SQL2)
The SQL2 CASE expression provides for limited decision making within SQL expressions. Its basic structure, shown in Figure 9-10, is similar to the IF…THEN…ELSE statement found in many programming languages. When the DBMS encounters a CASE expression, it evaluates the first search condition, and if it is TRUE, then the value of the CASE expression is the value of the first result expression. If the result of the first search condition is not TRUE, the DBMS proceeds to the second search condition and checks whether it is TRUE.If so, the value of the CASE expression is the value of the second result expression, and so on.
Here is a simple example of the use of the CASE expression. Suppose you want to do an A/B/C analysis of the customers from the sample database according to their credit limits. The A customers are the ones with credit limits over $60,000, the B customers are those with limits over $30,000 and the C customers are the others. Using SQL1, you would have to retrieve customer names and credit limits from the database and then rely on an application program to look at the credit limit values and assign an A, B, or C rating. Using a SQL2 CASE expression, you can have the DBMS do the work for you:
SELECT COMPANY,CASE WHEN CREDIT_LIMIT > 60000 THEN
A' WHEN CREDIT_LIMIT > 30000 THEN 'B'
ELSE 'C'
FROM CUSTOMERS
For each row of query results, the DBMS evaluates the CASE expression by first comparing the credit limit to $60,000, and if the comparison is TRUE, returns an A in the second column of query results. If that comparison fails, the comparison to $30,000 is made and returns a B if this second comparison is TRUE. Otherwise, the third column of query results will return a C.
This is a very simple example of a CASE expression. The results of the CASE expression are all literals here, but in general, they can be any SQL expression. Similarly, there is no requirement that the tests in each WHEN clause are similar, as they are here. The CASE expression can also appear in other clauses of a query. Here is an example of a query where it’s useful in the WHERE clause. Suppose you want to find the total of the salesperson’s sales, by office. If a salesperson is not yet assigned to an office, that person should be included in the total for his or her manager’s office. Here is a query that generates the appropriate office groupings:
SELECT CITY, SUM(SALES)
FROM OFFICES, SALESREPS
WHERE OFFICE =
CASE WHEN (REP_OFFICE IS NOT NULL) THEN REP_OFFICE
ELSE (SELECT REP_OFFICE
FROM SALESREPS AS MGRS
WHERE MGRS.EMPL_NUM = MANAGER)
The SQL2 standard provides a shorthand version of the CASE expression for the
common situation where you want to compare a test value of some kind to a sequence of data values (usually literals). This version of the CASE syntax is shown in Figure 9-11. Instead of repeating a search condition of the form:
test_value = value1
in each WHEN clause, it lets you specify the test_valuecalculation once. For example, suppose you wanted to generate a list of all of the offices, showing the names of their managers and the cities and states where they are located. The sample database doesn’t include state names, so the query must generate this information itself. Here is a query, with a CASE expression in the SELECT list, that does the job:
SELECT NAME, CITY, CASE OFFICE WHEN 11 THEN 'New York'
WHEN 12 THEN 'Illinois'
WHEN 13 THEN 'Georgia'
WHEN 21 THEN 'California'
WHEN 22 THEN 'Colorado'
FROM OFFICES, SALESREPS
WHERE MGR = EMPL_NUM
The COALESCE Expression (SQL2)
Figure 9-11. SQL2 CASE expression alternative syntax
The COALESCE Expression (SQL2)
One of the most common uses for the decision-making capability of the CASE expression is for handling NULL values within the database. For example, it’s frequently desirable to have a NULL value from the database represented by some literal value (such as the word “missing”) or by some default value when using SQL to generate a report. Here is a report that lists the salespeople and their quotas. If a salesperson has not yet been assigned a quota, assume that the salesperson’s actual year-to-date sales should be listed instead. If for some reason the actual year-to-date sales are also NULL (unknown), then a zero amount should be listed. The CASE statement generates the desired IF…THEN… ELSE logic:
SELECT NAME, CASE WHEN (QUOTA IS NOT NULL) THEN QUOTA WHEN (SALES IS NOT NULL) THEN SALES
ELSE 0.00
FROM SALESREPS
This type of NULL-handling logic is needed frequently, so the SQL2 standard includes a specialized form of the CASE expression, the COALESCE expression, to handle it. The syntax for the COALESCE expression is shown in Figure 9-12. The processing rules for the COALESCE expression are very straightforward. The DBMS examines the first value in the list. If its value is not NULL, it becomes the value of the COALESCE expression. If the first value is NULL, the DBMS moves to the second value and checks to see whether it is NULL. If not, it becomes the value of the expression. Otherwise, the DBMS moves to the third value, and so on. Here is the same example just given, expressed with the COALESCE expression instead of a CASE expression:
SELECT NAME, COALESCE (QUOTA, SALES, 0.00)
FROM SALESREPS
As you can see by comparing the two queries, the simplicity of the COALESCE syntax makes it easier to see, at a glance, the meaning of the query. However, the operation of the two queries is identical. The COALESCE expression adds simplicity, but no new capability, to the SQL2 language.
 This article is excerpted from MySQL The Complete Reference by Vikram Vaswani (McGraw-Hill, 2003; ISBN 0072224770). Check it out at your favorite bookstore today. Buy this book now. |
Next: The NULLIF Expression (SQL2) >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
/ 19 
