Numeric Operations

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:

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 the SQL predefined <data type> that you want to convert the value of "scalar_expression" into. (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 a CAST result of 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 -- for example: "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 (for example: "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 rounding 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 the source to obtain a valid target value. [OCELOT Implementation] The OCELOT DBMS that comes with this book truncates the source 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.,

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 is required 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 this character string: '0E0'.)

When you CAST an exact numeric value or an approximate numeric value to a variable length character string target or a CLOB or 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.

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 -- for example, "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 the source to fit the target and then make the assignment or (b) your DBMS may round the source to fit the target and then make the assignment. [OCELOT Implementation] The OCELOT DBMS that comes with this book truncates the source value 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 on whether your DBMS uses rounding or truncation to turn it into an integer. Here are the choices (note that "rounding toward zero" is really truncating):

Most DBMSs use truncation, but these 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.

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)".)

Other Operations:

With SQL, you have several other operations that you can perform on numbers, or on other values to get a numeric result.

Arithmetic

SQL provides the usual scalar arithmetic operators -- + and - and

• and / -- to perform operations on numbers. All of them will be familiar; there are equivalent operators in other computer languages. If any of the operands are NULL, the result of the operation is also NULL.

monadic + and monadic -- When used alone, + and - change the sign of an operand (e.g.: a <literal> or a Column instance or a host variable). For example:

SELECT -5, -(-occurrence_decimal)
FROM   Exact_Examples
WHERE  occurrence_integer = +5;

NOTE: Since two dashes (i.e.: --) means "comment start" in SQL, our example of a double negative has to be "-(-occurrence_decimal)" rather than "--occurrence_decimal".

dyadic + and dyadic - and dyadic * and dyadic / When used between two operands, + and - and * and / stand for add and subtract and multiply and divide, respectively, and return results according to the usual rules. For example:

SELECT occurrence_integer + 5, (occurrence_integer * 7) / 2
FROM   Exact_Examples
WHERE  occurrence_integer < (:host_variable - 7);

precedence Dyadic * and / have priority over dyadic + and -, but monadic + and - have top priority. It's good style to use parentheses for any expressions with different operators.

errors The two common arithmetic exception conditions are:

SQLSTATE 22003 -- data exception - numeric value out of range SQLSTATE 22012 -- data exception - division by zero

Here is a snippet of an embedded SQL program that checks for overflow after executing a statement that contains addition:

EXEC SQL UPDATE Exact_Examples
         SET    occurrence_smallint = occurrence_decimal + 1;
if (strcmp(sqlstate,"22003") 
printf("Overflow! Operation cancelled ...\n");

Error checks should follow every execution of an SQL statement, but imagine that the EXACT_EXAMPLES Table has a million rows. To avoid the situation where, after chugging through 999,999 rows, your application collapses on the last one with "Overflow! Operation cancelled ...", try this code:

EXEC SQL UPDATE Exact_Examples
         SET    occurrence_smallint =
         CASE
           WHEN occurrence_smallint = 32767 THEN 0
           ELSE occurrence_smallint = occurrence_smallint + 1
         END;

TIP: CASE expressions are good for taking error-abating actions in advance.

TIP: SQL has no low-level debugging features, so sometimes you will need to force an error somewhere in a complex expression, to be sure it is actually being executed. For this purpose, insert code that would cause a numeric overflow.

Mixing numeric <data type>s --

As we said earlier, all numbers -- any <data type>, exact or approximate -- are compatible. That means that you can mix them together in any numeric expression -- which leads to the question: what comes out when you do mix them, i.e.: what is the <data type>, precision and scale of the result? The SQL Standard says these are the results you will get:

[NON-PORTABLE] An exact numeric value added to, subtracted from, multiplied by or divided by an exact numeric value yields an exact numeric value with a precision that is non-standard because the SQL Standard requires implementors to define the precision of the result. For all these operations, if the result of the operation can't be exactly represented with the correct precision and scale, the operation will fail: your DBMS will return the SQLSTATE error 22003 "data exception-numeric value out of range". [OCELOT Implementation] The OCELOT DBMS that comes with this book gives the result of an arithmetic operation between exact numeric operands a <data type> and precision that matches the <data type> and precision of the operand with the most exact precision, e.g.: for an operation with SMALLINT and INT operands, the result is an INT type.

An exact numeric value added to or subtracted from an exact numeric value yields a result with a scale size that matches the size of scale of the operand with the largest scale, e.g.: for an operation with DECIMAL(6,2) and INT operands, the result has a scale of 2.

An exact numeric value multiplied by an exact numeric value yields a result with a scale size that is the sum of the scale sizes of the operands, e.g.: for an operation with DECIMAL(6,2) and NUMERIC(10,4) operands, the result has a scale of 6.

[NON-PORTABLE] An exact numeric value divided by an exact numeric value yields a result with a scale size that is non-standard because the SQL Standard requires implementors to define the scale size of the result. [OCELOT Implementation] The OCELOT DBMS that comes with this book gives the result of a division operation between exact numeric operands a scale size that matches the size of scale of the operand with the largest scale, e.g.: for an operation with DECIMAL(6,2) and NUMERIC(10,4) operands, the result has a scale of 4.

[NON-PORTABLE] An approximate numeric value added to, subtracted from, multiplied by or divided by an approximate numeric value yields an approximate numeric value with a precision and scale that are non-standard because the SQL Standard requires implementors to define the precision and scale of the result. If the exponent of the result doesn't fall within the DBMS's supported exponent range, the operation will fail: your DBMS will return the SQLSTATE error 22003 "data exception-numeric value out of range". [OCELOT Implementation] The OCELOT DBMS that comes with this book gives the result of an arithmetic operation between approximate numeric operands a <data type> and precision that matches the <data type> and precision of the operand with the most exact precision, e.g.: for an operation with REAL and DOUBLE PRECISION operands, the result is a DOUBLE PRECISION type.

[NON-PORTABLE] An approximate numeric value added to, subtracted from, multiplied by or divided by an exact numeric value (or vice versa) yields an approximate numeric value with a precision and scale that are non-standard because the SQL Standard requires implementors to define the precision and scale of the result. [OCELOT Implementation] The OCELOT DBMS that comes with this book gives the result of an arithmetic operation between approximate numeric and exact numeric operands a <data type> and precision that matches the <data type> and precision of the operand with the most exact precision, e.g.: for an operation with INT and DOUBLE PRECISION operands, the result is a DOUBLE PRECISION type.

In other words, the Standard always evades the big question: what's the result precision? To put this into perspective, consider a DBMS faced with a tough operation: "add 1 to a Column which is defined as DECIMAL(5)". Since the Column might already contain the value 99999, adding 1 might yield 100000 -- a DECIMAL(6) value. For such cases, the DBMS must decide what to do before executing, because the application program, which will receive the result, must know the size in advance. The DBMS has two choices:

• Let it grow. The result is DECIMAL(6) if the operation is addition and slightly more if the operation is multiplication. This choice has the advantage that it eliminates "overflow" errors. But there are still undefined areas: What happens if the DECIMAL precision is already at the maximum? What happens if the operation adds 1 to a SMALLINT -- does the <data type> upgrade to INTEGER so that the increased precision is valid?
• Chop it. The result is DECIMAL(5), regardless. This risks failure on even the most innocuous operations, but it's a simple rule to follow: output precision = input precision. Programmers can understand it.

These choices are not mutually exclusive and your DBMS might make different decisions for different operations.

TIP: Before you divide, decide how many digits should follow the decimal point in the result. The number will almost certainly be greater than the number you start with; for instance, "12/5" (dividing scale-0 integers) yields "2.4" (a scale-1 decimal number) -- you hope. Your DBMS may increase the scale automatically, but the Standard doesn't say it must. To force the result, use this SQL code:

CAST (12 AS DECIMAL(3,1))/5    -- yields 2.4

Incidentally, there are several bad ways to cast. This SQL code:

CAST ((12/5) AS DECIMAL(3,1))

will yield 2.0 if your DBMS doesn't increase the scale automatically -- be sure to CAST the source, not the result. This SQL code:

CAST (12 AS DECIMAL(2,1))/5

will cause an error -- be sure your source value fits in the CAST target.

Floating-point arithmetic --

If you want fast and complex floating-point arithmetic, buy a good Fortran compiler: SQL can't handle the fancy stuff. In particular:

• SQL lacks useful functions which in other languages are built-in, e.g.: the ability to detect NaN (Not a Number).
• SQL vendors are only obliged to define and to accept IEEE numbers. They can do arithmetic without paying any attention to the IEEE standard at all. In particular, some vendors may use the same routines for approximate numerics as they use for exact numerics, and exact is slower.

Still, you can do the basic arithmetic functions -- add, subtract, divide, multiply, compare -- provided you take sensible precautions.

Comparing Two Floating-point Numbers for Equality Think of the inexact result produced when 1/100 was converted to a binary fraction. Because of this, the following SQL code:

... WHERE float_column = 1.0E+1

will fail if, e.g.: the value of float_column was originally produced by summing 1/100 one hundred times. To get the "approximately right" answer, compare the absolute difference between the two numbers against a constant, e.g.: with this SQL code:

... WHERE ABS(float_column - 1.0E+1) < :epsilon

To choose a value for epsilon, remember that the accuracy of floating point numbers varies -- by definition -- according to magnitude. For example, between 1.0 and 2.0 there are about 8 million numbers, but between 1023.0 and 1024.0 there are only about 8 thousand numbers (assuming IEEE single-precision standards). In this example, since the comparison is for equality, we know that float_column must be about the same magnitude as the <literal> 1.0E+1, therefore a reasonable value for epsilon is 1/8000000 or 1.25E-7. When you don't know one of the comparands in advance, start with a value for epsilon that's half as large, multiply it by the sum of the comparands (thus changing its magnitude to the comparands' magnitude) and then compare with this SQL code:

... WHERE  ABS(float_column_1 - float_column_2) <
          (ABS(float_column_1 + float_column_2) * :epsilon/2)

Subtraction We did this operation with an IEEE-compatible compiler: 1234.567 - 1234.000 The result was: 0.5670166

Both inputs are single-precision floating point numbers (7 digits precision), accurate to the third decimal place. Unfortunately, so is the output. Although the subtraction decreased the magnitude, causing the decimal place to shift right, the accuracy was unaffected: the extra digits after 0.567 are spurious precision. If a subtraction causes a drop in magnitude, spurious precision is likely. (This is often called the "insignificant digits" problem and applies to addition too, if operands can have negative signs.)

TIP: Eliminate insignificant digits using two CASTs. In this example, we know what the input is, so we could clear everything after the result's third decimal place with this SQL code:

CAST (CAST ((1.234567E+04 - 1.234000E+04) AS DEC(8,3)) AS REAL)

Here, by casting to DEC(8,3) we first change the result 0.5670166 to 0.567. The second CAST casts this back to REAL, with a subsequent result of 0.5670000. Casting is a straightforward way to strip -- unfortunately, it's only useful if you know a lot about the data.

TIP: If an SQL statement does both addition and subtraction, parenthesize so that the addition happens first -- this makes a drop in magnitude less likely to occur. For example, change this SQL statement:

UPDATE Approximate_Examples
SET    occurrence_real = occurrence_real - :host_variable + 1.1E+01;

to this SQL statement:

UPDATE Approximate_Examples
SET    occurrence_real = occurrence_real - (:host_variable + 1.1E+01);

By the way, don't just transpose the operands. Order of expression evaluation varies.

Division When doing floating-point division, keep in mind that there is such a thing as "negative zero" and there are floating-point numbers which are so small that you'll get an exception when you divide by them, even though they don't exactly equal zero. This makes it a little harder to test for "division by zero" errors in advance.

Scalar Operations

SQL provides ten scalar functions that return a number: the <case expression>, the <cast specification>, the <position expression>, the three <length expression>s, the <extract expression>, the <cardinality expression>, the <absolute value expression> and the <modulus expression>. Only the last two also operate exclusively on numbers; these are described below. We'll discuss the rest in other chapters; for now, just remember that they evaluate to a number and can therefore be used anywhere in an SQL statement that a number could be used.

<absolute value expression> --

The required syntax for an <absolute value expression> is:

<absolute value expression> ::=
ABS(numeric_argument)

ABS operates on an argument that evaluates to a number. It strips a negative sign (if it's present) from the argument and returns a non-negative number whose <data type> is the same as the argument's <data type>, e.g.: ABS(-17) returns 17, ABS(17) returns 17 and ABS(0) returns 0. If the argument is NULL, ABS returns NULL.

If the result of ABS is a number that doesn't fit into the argument's <data type> range, the function will fail: your DBMS will return the SQLSTATE error 22003 "data exception-numeric value out of range".

ABS is new to SQL with SQL3 and is also supported by ODBC. If your DBMS doesn't support ABS, you can simulate it with this SQL statement:

CASE
  WHEN ...<0 THEN ...*-1
  ELSE ...
END

If your DBMS doesn't support CASE, you can still get an absolute value of a number with this arithmetic expression:

(number * number) / number

[Obscure Rule] ABS can also operate on an interval. We've ignored this option for now -- look for it in our chapter on temporal values.

<modulus expression> --

The required syntax for a <modulus expression> is:

<modulus expression> ::=
MOD(dividend_argument,divisor_argument)

MOD operates on two arguments, both of which must evaluate to an exact numeric integer. It divides the first number by the second number and returns the operation's remainder as a non-negative exact numeric integer whose <data type> is the same as the divisor_argument's <data type>, e.g.: MOD(35,4) returns 3 and MOD(32,4) returns 0. If either argument is NULL, MOD returns NULL. If the divisor_argument is zero, the function will fail: your DBMS will return the SQLSTATE error 22012 "data exception-division by zero".

MOD is new to SQL with SQL3. In the Standard, MOD stands for "modulus" but the result of this function is not actually a modulus -- it is a remainder achieved "by means of a modulus".

Set functions

SQL provides five set functions that return a number: COUNT(*), COUNT, AVG, SUM and GROUPING, all of which also operate on numbers. In addition to these, the set functions MAX and MIN also operate on numbers. Since none of these operate exclusively with numeric arguments, we won't discuss them here; look for them in Chapter 33 "Searching with Groups."

Predicates

In addition to the comparison operators, SQL provides eight other predicates that operate on numbers: the <between predicate>, the <in predicate>, the <null predicate>, the <exists predicate>, the <unique predicate>, the <match predicate>, the <quantified predicate> and the <distinct predicate>. Each will return a boolean value: either TRUE, FALSE or UNKNOWN. None of these operate strictly on numbers, so we won't discuss them here. Look for them in Chapter 29 "Simple Search Conditions."

Note:

Portions of the text in this entry are Copyright © 1999 by Ocelot Computer Services Incorporated. Used by permission.