Algebraic Functions

Algebraic functions take one or more arguments of numeric data types.

Algebraic Functions:
 
|--+-ABS--(--num_expression--)-----------------------------------+--|
   +-MOD--(--dividend , divisor--)-------------------------------+
   +-POW--(--base, exponent--)-----------------------------------+
   |                    .- ,-- 2-----.                           |
   +-ROOT--(--radicand--+------------+--)------------------------+
   |                    '- ,-- index-'                           |
   |                          (1)   .- ,-- 0---------------.     |
   +-ROUND--(--| Expression |-------+----------------------+--)--+
   |                                '- ,-- rounding_factor-'     |
   +-SQRT--(--sqrt_radicand--)-----------------------------------+
   |                          (1)   .- ,-- 0----------------.    |
   '-TRUNC--(--| Expression |-------+-----------------------+--)-'
                                    '- ,--  truncate_factor-'
 

1. See page Expression

ABS Function

The ABS function returns the absolute value of a numeric expression, returning the same data type as its single argument. The following example shows all orders of more than $20 paid in cash ( + ) or store credit ( - ). The stores_demo database does not contain any negative balances, but you might have negative balances in your application.

SELECT order_num, customer_num, ship_charge 
  FROM orders WHERE ABS(ship_charge) > 20

MOD Function

The MOD function returns the remainder from integer division of two real number operands, after the integer part of the first argument (the dividend) is divided by the integer part of the second argument (the divisor) as an INT data type (or INT8 for remainders outside the range of INT). The quotient and any fractional part of the remainder are discarded. The divisor cannot be 0. Thus, MOD (x,y) returns y (modulo x). Make sure that any variable that receives the result is of a data type that can store the returned value.

This example tests to see if the current date is within a 30-day billing cycle:

SELECT MOD(TODAY - MDY(1,1,YEAR(TODAY)),30) FROM orders

POW Function

The POW function raises the base to the exponent. This function requires two numeric arguments. The returned data type is FLOAT. The following example returns data for circles whose areas are less than 1,000 square units:

SELECT * FROM circles WHERE (3.1416 * POW(radius,2)) < 1000

To use e, the base of natural logarithms, see EXP Function.

ROOT Function

The ROOT function returns the root value of a numeric expression. This function requires at least one numeric argument (the radicand argument) and allows no more than two (the radicand and index arguments). If only the radicand argument is supplied, the value 2 is used as a default value for the index argument. The value 0 cannot be used as the value of index. The value that the ROOT function returns is a FLOAT data type.

The first SELECT statement in the following example takes the square root of the expression. The second takes the cube root of the expression.

SELECT ROOT(9) FROM angles         -- square root of 9
SELECT ROOT(64,3) FROM angles      -- cube root of 64

The SQRT function is equivalent to ROOT(x).

ROUND Function

The ROUND function returns the rounded value of an expression. The expression must be numeric or must be converted to numeric. If you omit the rounding factor specification, the value is rounded to a scale of zero, or to the units place. The range of 32 ( + and - ) refers to the precision of the returned value, relative to the first argument.

Positive-digit values indicate rounding to the right of the decimal point; negative-digit values indicate rounding to the left of the decimal point, as Figure 3 shows.

Figure 3. ROUND Function

The following example shows how you can use the ROUND function with a column expression in a SELECT statement. This statement displays the order number and rounded total price (to zero places) of items whose rounded total price (to zero places) is equal to 124.00.

SELECT order_num , ROUND(total_price) FROM items
   WHERE ROUND(total_price) = 124.00

If you use a MONEY data type as the argument for the ROUND function and you round to zero places, the value displays with .00. The SELECT statement in the following example rounds an INTEGER value and a MONEY value. It displays 125 and a rounded price in the form xxx.00 for each row in items.

SELECT ROUND(125.46), ROUND(total_price) FROM items

SQRT Function

The SQRT function returns the square root of a numeric expression. The next example returns the square root of 9 for each row of the angles table:

SELECT SQRT(9) FROM angles

TRUNC Function

The TRUNC function returns the truncated value of a numeric expression.

The expression must be numeric or a form that can be converted to a numeric expression. If you omit the truncate factor specification, the argument is truncated to a scale of zero, or to the unit place. The range of 32 ( + and - ) refers to the precision of the returned value, relative to the first argument.

Positive digit values indicate truncating to the right of the decimal point; negative digit values indicate truncating to the left, as Figure 4 shows.

Figure 4. TRUNC Function

If a MONEY data type is the argument for the TRUNC function that specifies zero places, the fractional places are removed. For example, the following SELECT statement truncates a MONEY value and an INTEGER value. It displays 125 and a truncated price in integer format for each row in items.

SELECT TRUNC(125.46), TRUNC(total_price) FROM items