Implicit data type conversions are possible in SQL implementations.
For example, if a smallint is compared to an int in T-SQL, the smallint is
implicitly converted to int before the comparison proceeds.
See BOL for details about implicit and explicit conversions in MS SQL Server.
Example. Find the average price of laptops with the prefix text "Average price = ".
If you run the query
SELECT 'Average price = ' + AVG(price) FROM laptop;
|
the following error message will be obtained:
Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.
This message implies that the system cannot accomplish implicit conversion
of the character constant "Average price = " to data type money of the field
price.
In such cases, explicit type conversion can help.
In so doing, as the above message says, you can use the
CONVERT function.
However this function is not based on the SQL-92 standard. Because of this,
it is preferable to use the standard function
CAST. So, we start with
CAST.
If we rewrite above query as follows
SELECT 'Average price = ' + CAST(AVG(price) AS CHAR(15)) FROM laptop;
|
we get desired result:
We have used the expression for explicit type conversion
CAST
to bring the average value to the character view.
The statement
CAST has very simple syntax:
CAST(<expression> AS <data type>)
Firstly, it should be noted that some data type conversions are not supported.
(SQL-92 Standard involves the table of allowed conversions). Secondly, NULL-value is converted into
NULL-value also.
Let us consider another example: Define the average launching year
from the Ships table. The query
SELECT AVG(launched) FROM ships;
|
gives 1926. In principle, it is correct, because a year is integer number.
However arithmetic mean will be about 1926,2381. It should be noted that
aggregate functions (except
COUNT which always returns integer value)
inherits the type of data to be processed. Because the
launched field is
integer-valued, we have gotten the average value without fractional part
(not rounded off).
What must we do if the result ought to be obtained with two digits after decimal point?
As mentioned above, applying the
CAST statement to the average value
gives no result. Indeed,
SELECT CAST(AVG(launched) AS NUMERIC(6,2)) FROM ships;
|
returns the value of 1926.00. Consequently, the
CAST statement
should be applied to the argument of the aggregate function:
SELECT AVG(CAST(launched AS NUMERIC(6,2))) FROM ships;
|
The result - 1926.238095 - is not exactly correct. This is because of
implicit conversion that was accomplished when calculating the average value.
Another step:
SELECT CAST(AVG(CAST(launched AS NUMERIC(6,2))) AS NUMERIC(6,2)) FROM ships;
|
It is the correct result - 1926.24. However this solution looks too cumbersome.
Let implicit conversion to work for us:
SELECT CAST(AVG(launched*1.0) AS NUMERIC(6,2)) FROM ships;
|
Thus, we use implicit conversion of the argument from integer to exact numeric type
by multiplying it by real unity. After that, explicit conversion is
applied to the result of the aggregate function.
The same conversions can be made with aid of the
CONVERT function:
SELECT CONVERT(NUMERIC(6,2),AVG(launched*1.0)) FROM ships;
|
The
CONVERT function has the following syntax:
CONVERT (<data type>[(<length>)], <expression> [, <style>])
The main distinction of the CONVERT function from the CAST
statement is that the first allows formatting data (for example,
temporal data of datetime type) when converting them to
character data and specifying the format when converting character data to datetime.
The values of integer optional argument style correspond
to different formats. Let us consider the following example
SELECT CONVERT(char(25),CONVERT(datetime,'20030722'));
|
Here, the string representation of a date is converted to
datetime
following the reverse conversion to demonstrate the result of formatting.
Since the style argument is omitted, default value is used (0 or 100).
As a result, we obtain
Below are some values of the
style argument and corresponding results
from the above example. Note, the
style values
greater than 100 give four-place year.
| style |
format |
| 1 |
07/22/03 |
| 11 |
03/07/22 |
| 3 |
22/07/03 |
| 121 |
2003-07-22 00:00:00.000 |
All possible values of the
style argument are given in BOL.
CASE statement
Let the list of all the models of PC is required along with their prices.
Besides that, if the model is not on sale (not in PC table), in the place of price
must be the text "Not available".
The list of all the PC models with its prices we can obtain running the query:
SELECT DISTINCT product.model, price FROM product LEFT JOIN pc c
ON product.model=c.model
WHERE product.type='pc';
|
Missing prices will be replaced by NULL-values:
| model |
price |
| 1121 |
850 |
| 1232 |
350 |
| 1232 |
400 |
| 1232 |
600 |
| 1233 |
600 |
| 1233 |
950 |
| 1233 |
980 |
| 1260 |
350 |
| 2111 |
NULL |
| 2112 |
NULL |
The
CASE statement helps us to get required text instead of NULL:
SELECT DISTINCT product.model,
CASE WHEN price IS NULL THEN 'Not available' ELSE CAST(price AS CHAR(20)) END price
FROM product LEFT JOIN pc c ON product.model=c.model
WHERE product.type='pc'
|
Depending on defined conditions, the
CASE statement returns one of the possible
values. The condition in above example is the checking for NULL.
If this condition is satisfied, the text "Not available" will be returned;
otherwise (
ELSE) it will be the price.
One principal moment is here. As a table is always the result of the
SELECT statement,
all values from any column must be of the same data type (having regard to implicit
type conversions). Then, we cannot combine character constant with a price (numeric type)
within a single column. That is the reason why we use the type conversion to the price
column to reduce its type to character. As a result, we get
| model |
price |
| 1121 |
850 |
| 1232 |
350 |
| 1232 |
400 |
| 1232 |
600 |
| 1233 |
600 |
| 1233 |
950 |
| 1233 |
980 |
| 1260 |
350 |
| 2111 |
Not available |
| 2112 |
Not available |
The CASE statement may be used in one of two syntax forms:
The first form
CASE <input expression>
WHEN <when expression 1>
THEN <return expression 1>
…
WHEN <when expression N>
THEN <return expression N>
[ELSE <return expression>]
END
Second form
CASE
WHEN <predicate 1>
THEN <return expression 1>
…
WHEN <predicate N>
THEN <return expression N>
[ELSE <return expression>]
END
All WHEN clauses must be in the same syntax form, i.e. first and second forms
cannot be mixed. When using the first syntax form, the WHEN
condition is satisfied as soon as the value of when expression will
be equal to the value of input expression. When using the second
syntax form, the WHEN condition is satisfied as soon as the predicate
evaluates to TRUE. When satisfying condition, the CASE statement returns
the return expression from the corresponding THEN clause.
If no WHEN expression is satisfied,
the return expression from the ELSE clause will be used.
If no ELSE clause is specified, a NULL value will be returned.
If more than one condition are satisfied, the first return expression
from them will be returned.
The above example uses the second form of the CASE statement.
It should be noted that checking for NULL could also be made using the
standard function COALESCE, which is simpler.
This function has arbitrary number of arguments and returns the first not-NULL
expression among them.
In the case of two arguments, COALESCE(A, B) is equivalent to the following
CASE statement:
CASE WHEN A IS NOT NULL THEN A ELSE B END
When using the
COALESCE function, the solution to the above example
may be rewritten as follows
SELECT DISTINCT product.model,
COALESCE(CAST(price as CHAR(20)),'Not available') price
FROM product LEFT JOIN pc c ON product.model=c.model
WHERE product.type='pc';
|
Usage of the first syntax form of the
CASE statement can be demonstrated
by the following
example: Get all available PC models, their prices,
and information about the most expensive and cheap models.
SELECT DISTINCT model, price,
CASE price WHEN (SELECT MAX(price) FROM pc) THEN 'Most expensive'
WHEN (SELECT MIN(price) FROM pc) THEN 'Most cheap'
ELSE 'Mean price' END comment
FROM pc ORDER BY price;
|
The result set
| model |
price |
comment |
| 1232 |
350 |
Most cheap |
| 1260 |
350 |
Most cheap |
| 1232 |
400 |
Mean price |
| 1233 |
400 |
Mean price |
| 1233 |
600 |
Mean price |
| 1121 |
850 |
Mean price |
| 1233 |
950 |
Mean price |
| 1233 |
980 |
Most expensive |
Suggested exercises:
31, 32, 47, 53, 54.