A number is compatible with, and comparable to, all other numbers — that is, all numbers are mutually comparable and mutually assignable. Numbers may not be directly compared with, or directly assigned to, any other <data type> class though implicit type conversions can occur in expressions, SELECTs, INSERTs, DELETEs, and UPDATEs. Explicit numeric type conversions can be forced with the CAST operator.
CAST
In SQL, CAST is a scalar operator that converts a given scalar value to a given scalar <data type>. The required syntax for the CAST operator is as follows.
CAST (<cast operand> AS <cast target>) <cast operand> ::= scalar_expression
<cast target> ::= <Domain name> | <data type>
The CAST operator converts values of a source <data type> into values of a target <data type>
where each <data type> is an SQL pre-defined <data type> (data conversions between UDTs are done with a user-defined cast). The source <data type> (or <cast operand>) can be any
expression that evaluates to a single value. The target <data type> (or <cast target>) is either an SQL predefined <data type> specification or the name of a Domain whose defined <data type> is your target <data type>. If you use CAST (. . . AS <Domain name>), your current
<AuthorizationID> must have the USAGE Privilege on that Domain.
It isn't, of course, possible to convert the values of every <data type> into the values of every other <data type>. For numbers, the rules are:
• CAST (NULL AS <data type>) and CAST (numeric_source_is_a_null_value AS
<data type>) both result in NULL.
• You can CAST an exact numeric source to these targets: exact numeric, approximate numeric, fixed length character string, variable length character string, CLOB, and NCLOB. You can also CAST an exact numeric source to an interval target, provided the target contains only one
datetime field. That is, you can CAST an integer to INTERVAL YEAR or to INTERVAL MONTH, but you can't CAST it to INTERVAL YEAR TO MONTH. You can CAST an exact numeric source to a UDT target or a <reference type> target if a user-defined cast exists for this purpose and your current <AuthorizationID> has the EXECUTE Privilege on that user-defined cast.
• You can CAST an approximate numeric source to these targets: exact numeric, approximate numeric, fixed length character string, variable length character string, CLOB, and NCLOB. You can also CAST an approximate numeric source to a UDT target or a <reference type> target if a user-defined cast exists for this purpose and your current <AuthorizationID> has the EXECUTE Privilege on that user-defined cast.
When you CAST an exact numeric value or an approximate numeric value to an exact numeric target, e.g., CAST (25 AS INTEGER)), CAST (1.47E-5 AS DECIMAL(9,7)), or when you CAST an exact numeric value or an approximate numeric value to an approximate numeric target, e.g., CAST (25 AS FLOAT), CAST (1.47E-5 AS DOUBLE
PRECISION) — your DBMS checks whether the source is a valid value for the target's <data type> or if a valid value (one that doesn't lose any leading significant digits) can be obtained from the source by roundingcontinue
Page 59
or truncation. If so, then the source is converted to that target value. If neither of these are true, the CAST will fail; your DBMS will return the SQLSTATE error 22003 "data
exception-numeric value out of range."
[NON-PORTABLE] If your source value is not a valid value for your target <data type>, then the value CAST is non-standard because the SQL Standard requires implementors to define whether the DBMS will round or will truncate to obtain a valid target value. [OCELOT Implementation]
The OCELOT DBMS that comes with this book truncates to obtain a valid target value.
When you CAST an exact numeric value or an approximate numeric value to a fixed length character string target, your DBMS converts the number to the shortest string that represents that number, i.e.,
CAST (25 AS CHAR(2)) result in the character string '25'
CAST (1.47E-5 AS CHAR(8)) result in the character string '.0000147' CAST (-25 AS CHAR(3)) results in the character string '-25'
CAST (+25 AS CHAR(3)) results in the character string '25' CAST (025 AS CHAR(3)) results in the character string '25' CAST (25. AS CHAR(3)) results in the character string '25' CAST (25.0 AS CHAR(4)) results in the character string '25'
. . .
If the length of the result equals the fixed length of the target, then the source is CAST to that result. If the length of the result is shorter than the fixed length of the target, then the source is CAST to that result, padded on the right with however many spaces are needed to make the lengths the same. If the length of the result is longer than the fixed length of the target, the CAST will fail; your DBMS will return the SQLSTATE error 22001 "data
exception-string data, right truncation." And if the result contains any characters that don't belong to the target's Character set, the CAST will also fail; your DBMS will return the SQLSTATE error 22018 "data exception-invalid character value for cast."
NOTE: If your approximate numeric source value is zero, the CAST result is the character string 'OEO'.
When you CAST an exact numeric value or an approximate numeric value to a variable length character string target, a CLOB target, or an NCLOB target, your DBMS converts the number to the shortest string that represents that number. As with fixed length target, it strips off leading plus signs, leading zeros, and any insignificant decimal signs and trailing zeros. If the length of the result is less than or equals the maximum length of the target, then the source is CAST to that result. If the length of the result is longer than the maximum length of the target, the CAST will fail; your DBMS will return the SQLSTATE error 22001 "data exception-string data, right truncation." And if the result contains any characters that don't belong to the target's Character set, the CAST will also fail; your DBMS will return the SQLSTATE
error 22018 "data exception-invalid character value for cast."
[Obscure Rule] The result of a CAST to a character string target has the COERCIBLE coercibility attribute; its Collation is the default Collation for the target's Character set.break
Page 60
When you CAST an exact numeric value to an interval target, your DBMS converts it to the value of the interval's single datetime field represented by that number, i.e., CAST (25 AS
INTERVAL YEAR) results in an interval of 25 years. If the number you're casting is too large for the precision of the target, as in CAST (500 AS INTERVAL HOUR(2)) — the CAST will fail; your DBMS will return the SQLSTATE error 22015 "data
exception-interval field overflow."
When you CAST an exact numeric value or an approximate numeric value to a UDT or a
<reference type> target, your DBMS invokes the user-defined cast routine with the source value as the routine's argument. The CAST result is the value returned by the user-defined cast.
If you want to restrict your code to Core SQL, don't use <Domain name> as a CAST target;
CAST only to a <data type>.
Assignment
In SQL, when an exact numeric or an approximate numeric value is assigned to an exact numeric target, the source is first converted to an exact numeric value with the precision and scale of the target. When an exact numeric or an approximate numeric value is assigned to an approximate numeric target, the source is first converted to an approximate numeric value with the precision of the target. In either case, if the assignment would result in the loss of any of the source value's most significant digits, the assignment will fail; your DBMS will return the SQLSTATE error 22003 "data exception-numeric value out of range."
[NON-PORTABLE] If the assignment of a numeric value would result in the loss of any of the source value's least significant digits, the result is non-standard because the SQL Standard requires implementors to define the result using either of two options: (a) your DBMS may truncate to fit the target and then make the assignment or (b) your DBMS may round to fit the target and then make the assignment. [OCELOT Implementation] The OCELOT DBMS that comes with this book truncates to fit the target.
[Obscure Rule] Since only SQL accepts null values, when a null value is taken from SQL-data to be assigned to a numeric target, your target's value is not changed. Instead, your DBMS will set the target's indicator parameter to -1 to indicate that an assignment of the null value was
attempted. If your target doesn't have an indicator parameter, the assignment will fail; your DBMS will return the SQLSTATE error 22002 "data exception-null value, no indicator parameter." Going the other way, there are two ways to assign a null value to an SQL-data target. Within SQL, you can use the <keyword> NULL in an INSERT or an UPDATE statement to indicate that the target should be set to NULL; that is, if your source is NULL, your DBMS will set your target to NULL. Outside of SQL, if your source has an indicator parameter that is set to -1, your DBMS will set your target to NULL (regardless of the value of the source). An indicator parameter with a value less than -1 will cause an error; your DBMS will return the SQLSTATE error 22010 "data exception-invalid indicator parameter value." We'll talk more about indicator parameters in our chapters on SQL binding styles.
As an example, assume that you have an INTEGER Column and need to assign a non-integer value to it. The result will depend not only on what the source value is, but also oncontinue
Page 61
whether your DBMS uses rounding or truncation to turn it into an integer. The following table displays the choices (note that "rounding toward zero" is really truncating).
Source value
+infinity -infinity zero nearest
1.5 2 1 1 2
-1.5 1 -2 -1 -2
etc.
Most DBMSs use truncation but the following SQL statements show how to force the rounding method you prefer:
-- rounding toward positive infinity
CASE numeric_expression - CAST (numeric_expression AS INTEGER) WHEN > 0 numeric_expression+1
WHEN < 0 numeric_expression-1 ELSE numeric_expression
END
-- rounding toward negative infinity CASE numeric_expression
WHEN > 0 CAST (numeric_expression AS INTEGER)
WHEN < 0 CAST (0 - (ABS(numeric_expression) + 0.5) AS INTEGER)) ELSE numeric_expression
END
-- rounding toward zero
CAST (numeric_expression AS INTEGER) -- rounding toward nearest
CAST (numeric_expression + 0.5 AS INTEGER)
Comparison
SQL provides the usual scalar comparison operators, = and <> and < and <= and> and >=, to perform operations on numbers. All of them will be familiar; there are equivalent operators in other computer languages. Numbers are compared in the usual manner. If any of the comparands are NULL, the result of the operation is UNKNOWN. For example:
97 = 105.2
returns FALSE.
97 <> {result is NULL}
returns UNKNOWN.break
Page 62
SQL also provides three quantifiers — ALL, SOME, ANY — which you can use along with a
comparison operator to compare a value with the collection of values returned by a <table subquery>. Place the quantifier after the comparison operator, immediately before the <table subquery>. For example:
SELECT decimal_column FROM Table_1
WHERE decimal_column < ALL ( SELECT integer_column FROM Table_2);
ALL returns TRUE either (a) if the collection is an empty set, i.e., if it contains zero rows or (b) if the comparison operator returns TRUE for every value in the collection. ALL returns FALSE if the comparison operator returns FALSE for at least one value in the collection.
SOME and ANY are synonyms. They return TRUE if the comparison operator returns TRUE for at least one value in the collection. They return FALSE either (a) if the collection is an empty set or (b) if the comparison operator returns FALSE for every value in the collection. The search condition = ANY (collection) is equivalent to IN (collection).