Additional functions

Mathematical FUNCTIONS

Oracle has a large number of functions for manipulating columns that store NUMBER data. Aggregate row functions used to manipulate numeric data were covered in Chapter 5. In this chapter, you will learn about numeric functions that act on single values and that perform special mathematical manipulations.

Single-Value Functions

You may find that the single-value functions are really quite simple. These functions can be combined with the arithmetic operator symbols for addition, subtraction, multiplication, and division (+ - * /) to develop complex expressions for display in result tables. Numeric functions accept numeric arguments, such as expressions or column names defined as data type NUMBER, and return numeric values. The values returned are generally accurate to 38 decimal digits. Table 10.3 lists most of the single-value numeric functions and their use and definition. We will examine several of these functions in detail.

Table 10.3

Function	Use/Definition
ABS	The absolute value of a number. ABS(-5) returns 5
ACOS	The arc cosine of a number. Arguments must be between -1 and 1 and the returned value ranges from 0 to pi in radians. ACOS(0.5) = 1.047
ASIN	The arc sine of a number. Arguments must be between -1 and 1 and the returned value ranges from pi/2 to pi/2 in radians. ASIN(0.5) = 0.534
ATAN	The arc tangent of a number. Arguments are unbounded and the returned value ranges from -pi/2 to pi/2 in radians. ATAN(0.5) = 0.464
ATAN2	The arc tangent of two related numbers. Arguments are unbounded and the returned value ranges from -pi to pi. ATAN2(0.5, 5.0) = 0.0997
CEIL	The smallest integer value that is greater than a number. CEIL(6.6) = 7
COS	The cosine of a number expressed in radians. COS(0.5) = 0.8776
COSH	The hyperbolic cosine of a number. COSH(0.5) = 1.128
EXP	The value of the mathematical constant 'e' raised to the nth power. EXP(1) = 2.718
FLOOR	The largest integer value that is less than or equal to a number. FLOOR(6.7) = 6
LN	The natural logarithm of a number where the number is greater than zero. LN(0.5) = -0.693
LOG	The logarithm of base number1 of number2. Number1 is any base other than 0 or 1 and number2 is any positive number. LOG(10, 0.5) = -0.301
MOD	The modulus division function, returns the remainder of number1 divided by number2. MOD(7, 3) = 1
NVL	A replacement function, if NumericValue1 is NULL, NVL returns value2; otherwise, NVL returns NumericValue1. NVL(HoursWorked,'Zero') substitutes a value of Zero for any NULL value in the HoursWorked column.
POWER	The number1 raised to the number2 power. If number1 is negative, number2 must be an integer. POWER(5, 0.5) = 2.236
ROUND	Rounds a number1 to number2 decimal places. ROUND(15.34563, 2) = 15.35
SIGN	Evaluates number1; returns -1 if number1 is negative; 0 if number1 is 0; 1 if number1 is positive. SIGN(0.5) = 1
SIN	The Sine of a number expressed in radians. SIN(0.5) = 0.479
SINH	The Hyperbolic Sine of a number expressed in radians. SINH(0.5) = 0.521
SQRT	The square root of a number; the number must be positive. SQRT(5) = 2.236
TAN	The Tangent of a number expressed in radians. TAN(0.5) = 0.546
TANH	The Hyperbolic Tangent of a number expressed in radians. TANH(0.5) = 0.462
TRUNC	Truncates a number1 to number2 decimal places. TRUNC(15.34563,2) = 15.34

Transcendental Functions

The transcendental functions include the single-value functions named ACOS, ASIN, ATAN, ATAN2, COS, COSH, EXP, LN, LOG, SIN, SINH, TAN, and TANH. Business programming rarely requires these functions, and we will not define each of them here. For the most part, they are used in scientific applications. There is also some applicability in the area of quantitative business areas such as finance and marketing research. The query in SQL Example 10.11 demonstrates how to generate values for selected transcendental functions from the dual table. Recall that the dual table automatically exists in every Oracle database.
/* SQL Example 10.11 */

SELECT COS(0.5), EXP(1), LN(0.5), LOG(10,0.5)

FROM Dual;
COS(0.5) EXP(1) LN(0.5) LOG(10,0.5)

---------- ---------- ---------- -----------

.877582562 2.71828183 -.69314718 -.30103

NVL Function for NULL Value Substitution

You have learned that NULL values actually represent an unknown value. Sometimes when values in tables are unknown, it is possible to substitute a reasonable guess or average value where a NULL value exists. At other times you may wish to highlight the absence of a value by substituting another value, such as zero for the NULL value. The query in SQL Example 10.12 reveals that some employees have NULL values for the HoursWorked column in the projectAssignment table for projects 1, 6, and 7.
/* SQL Example 10.12 */

COLUMN Employee ID FORMAT A10;

COLUMN "Hours" FORMAT 9990.9

SELECT EmployeeID "Employee ID", ProjectNumber "Project",

HoursWorked "Hours"

FROM ProjectAssignment

WHERE ProjectNumber IN (1, 6, 7);
Employee ID Project Hours

----------- ---------- -------

23232 1 14.2

66425 6

23100 7

66532 7 14.8

67555 7 12.2
Suppose that the senior project manager requests that you produce a result table and substitute the value 0.0 where NULL values exist in the projectAssignment table. You can accomplish this task with the NVL function. NVL is a substitution function that allows you to substitute a specified value where the stored value in a row is NULL. The general format of the NVL function is:
NVL(Value1, Value2)
The NVL function works with character, date and other data types as well as numbers. If Value1 is NULL, NVL returns Value2; otherwise, NVL returns Value1. The following query in SQL Example 10.13 will produce the result requested by the senior project manager by listing a value of 0 where HoursWorked is NULL.
/* SQL Example 10.13 */

SELECT EmployeeID "Employee ID", ProjectNumber "Project",

NVL(HoursWorked, 0) "Hours"

FROM ProjectAssignment

WHERE ProjectNumber IN (1, 6, 7);
Employee ID Project Hours

----------- ---------- -------

23232 1 14.2

66425 6 0.0

23100 7 0.0

66532 7 14.8

67555 7 12.2

ABS Function

The absolute value is a mathematical measure of magnitude. The general format of the ABS function is:
ABS(value)
Oracle provides the ABS function for use in computing the absolute value of a number or numeric expression. Business managers may be interested in the magnitude by which a particular value deviates from some standard or average value. For example, suppose that the senior project manager has established 20 hours as the desired standard for working on assigned projects. The manager may wish to know which employees have deviated significantly from this standard on a project, either by not working enough (less than 10 hours) or by exceeding expectations (more than 30 hours). The query in SQL Example 10.14 addresses the senior project manager's concerns.
/* SQL Example 10.14 */

COLUMN "Hours" FORMAT 9990.9

COLUMN "Avg Hrs" FORMAT 9990.99

COLUMN "Difference" FORMAT 90.99

SELECT EmployeeID "Employee ID", HoursWorked "Hours",

ABS(HoursWorked - 20) "Difference"

FROM ProjectAssignment

WHERE ABS(HoursWorked - 20) >= 10

ORDER BY ABS(HoursWorked - 20);
Employee ID Hours Difference

----------- ------- ----------

33344 9.5 10.50

88777 30.8 10.80

88505 34.5 14.50

33344 5.1 14.90

67555 35.4 15.40

33358 41.2 21.20

6 rows selected.
The result table lists each employee identifying number, hours worked, and the number of hours by which the hours worked deviates from the standard of 20 hours. Note how the ABS function is used in the SELECT clause to compute the Difference column, in the WHERE clause to specify the criteria for row selection, and in the ORDER BY clause to order the result table by the degree of work deviation.

POWER and SQRT Functions

The POWER and SQRT (square root) functions are typically used in scientific computing, but may also be used in writing expressions for queries in the financial or marketing research areas. The general format for these functions is:
POWER(NumericValue1, NumericValue2)
SQRT(NumericValue)
The POWER function raises numeric NumericValue1 to a specified positive exponent, NumericValue2. The SQRT function computes the square root of a numeric value, expression or NUMBER column value. You may also note that SQRT(NumericValue) is equivalent to POWER(NumericValue, 0.5). SQL Example 10.15 demonstrates the use of these functions by selecting from the dual pseudo-table.
/* SQL Example 10.15 */

SELECT POWER(10, 3), POWER(25, 0.5), SQRT(25)

FROM Dual;
POWER(10,3) POWER(25,0.5) SQRT(25)

----------- ------------- ----------

1000 5 5

ROUND and TRUNC Functions

The ROUND and TRUNC (truncate) functions can display numeric values to specific levels of mathematical precision. The general format for these functions is:
ROUND(NumericValue1, IntegerValue)
TRUNC(NumericValue1, IntegerValue)
The ROUND function rounds NumericValue1 to the specified number of digits of precision, an integer value shown in the formal definition as IntegerValue. The TRUNC function drops digits from a number. Usually, we round or truncate numbers to some number of digits after the decimal. Suppose that a manager needs a listing of hours worked by employees assigned to projects, but wishes the listing to be rounded to the nearest whole hour. Perhaps a different manager wishes to simply truncate the hours worked to the nearest whole hour. The query in SQL Example 10.16 produces a result table that supports both managerial needs. Note that the highlighted rows show differences in output produced by rounding versus truncating.
/* SQL Example 10.16 */

SELECT EmployeeID "EmployeeID", HoursWorked "Hours",

ProjectNumber "Project", ROUND(HoursWorked,0) "Rounded",

TRUNC(HoursWorked,0) "Truncated"

FROM ProjectAssignment

ORDER BY EmployeeID;

EmployeeID Hours Project Rounded Truncated

---------- ------- ---------- ---------- ----------

01885 10.2 3 10 10

23100 10.3 4 10 10

23100 7

23232 14.2 1 14 14

23232 10.6 2 11 10

23232 8

33344 5.1 4 5 5

33344 9.5 5 10 9

more rows will be displayed…

List Functions—GREATEST and LEAST

The list functions, GREATEST and LEAST enable you to extract values from a group of columns. The general format for these two functions is:
GREATEST(Column1, Column2, ... )
LEAST(Column1, Column2, ... )
Contrast this with aggregate functions that work on groups of rows. Suppose that we have a data table that tracks the total hours worked by employees on a monthly basis over the course of three months. Table 10.4 depicts this type of data.

Table 10.4

EmployeeID	January Hours	February Hours	March Hours
01885	162.5	158.9	157.7
23100	165.5	170.4	177.4
23232	158.9	161.2	160.8
33344	146.4	188.2	178.2
More rows….

Using the sequence of commands in SQL Example 10.17, create a table like Table 10.4 and name it MonthlyHours. You also need to populate the table by inserting the values from Table 10.4 into the MonthlyHours data table.

/* SQL Example 10.17 */

CREATE TABLE MonthlyHours (

EmployeeID CHAR(5),

JanuaryHours NUMBER(5,1),

FebruaryHours NUMBER(5,1),

MarchHours NUMBER(5,1) );

INSERT INTO MonthlyHours VALUES ('01885', 162.5, 158.9, 157.7);

INSERT INTO MonthlyHours VALUES ('23100', 165.5, 170.4, 177.4);

INSERT INTO MonthlyHours VALUES ('23232', 158.9, 161.2, 160.8);

INSERT INTO MonthlyHours VALUES ('33344', 146.4, 188.2, 178.2);

Now suppose that a project manager needs to know both the greatest and least number of hours each employee worked during the three-month period. The query in SQL Example 10.18 uses the GREATEST and LEAST functions to produce the desired result table.
/* SQL Example 10.18 */

SELECT EmployeeID "Employee ID",

GREATEST(JanuaryHours, FebruaryHours, MarchHours) "Greatest Hrs",

LEAST(JanuaryHours, FebruaryHours, MarchHours) "Least Hrs"

FROM MonthlyHours;
Employee ID Greatest Hrs Least Hrs

----------- ------------ ----------

01885 162.5 157.7

23100 177.4 165.5

23232 161.2 158.9

33344 188.2 146.4