From Floor to Ceiling and Other Functional Cases

Part 7 in a series on the basics of the relational database and SQL

September/October 2012

Part 6 in this series, “A Function of Character” (Oracle Magazine, July/August 2012), introduced SQL character functions (also known as string functions or text functions) and showed how your queries can use them to modify the appearance of character result set data. Similarly, you can use SQL number functions to manipulate numerical data so that it displays differently from how it is stored in the database. This article introduces you to some of the more commonly used SQL number functions, along with some useful miscellaneous other functions.

To try out the examples in this and subsequent articles in the series, you need access to an Oracle Database instance. If necessary, download and install an Oracle Database edition for your operating system. I recommend installing Oracle Database, Express Edition 11g Release 2.

If you install the Oracle Database software, choose the installation option that enables you to create and configure a database. A new database, including sample user accounts and their associated schemas, will be created for you. (Note that SQL_101 is the user account you’ll use for the examples in this series; it’s also the schema in which you’ll create database tables and other objects.) When the installation process prompts you to specify schema passwords, enter and confirm passwords for SYS and SYSTEM and make a note of them.

Finally—whether you installed the database software from scratch or have access to an existing Oracle Database instance—download, unzip, and execute the SQL script to create the tables for the SQL_101 schema that are required for this article’s examples. (View the script in a text editor for execution instructions.) Some of the examples also use the DUAL table. Recall that DUAL is an Oracle system table owned by the SYS user, not the SQL_101 schema. DUAL contains no meaningful data itself, but it is useful to query it as a way to experiment with functions that work on literals.

One frequently used number function, ROUND, enables you to round a numeric value that is returned in a result set. For example, the simple query in Listing 1 uses this function to apply conventional rounding to two numbers. One number is rounded down, and the other is rounded up.

Code Listing 1: Using ROUND function to round one number up and another number down

SQL> set feedback on
SQL> select ROUND(7534.1238, 2), ROUND(99672.8591, 2)
  2    from dual;
ROUND(7534.1238,2)  ROUND(99672.8591,2)
——————————————————  ———————————————————
           7534.12             99672.86
1 row selected.

Number functions require input parameters that are numeric—either a column with a NUMBER datatype or a numeric literal. ROUND takes two parameters, one required and one optional. The required parameter is the numeric value to be rounded. The optional parameter is an integer that indicates the rounding precision—that is, how many places to the right (indicated by a positive integer) or left (indicated by a negative integer) of the decimal point the numeric value should be rounded to. The query in Listing 1 applies the ROUND number function to two numeric literal values. Both numbers are rounded to two digits to the right of the decimal point.

If you omit the second parameter, the ROUND function will round the numeric value to the nearest whole number, as shown in Listing 2. The query in Listing 3 demonstrates that if you pass the optional parameter a negative integer, the ROUND function will round the numeric value on the left side of the decimal point.

Code Listing 2: Using ROUND function to round numeric values to whole numbers

SQL> select ROUND(7534.1238),  ROUND(99672.8591)
  2    from dual;
ROUND(7534.1238)  ROUND(99672.8591)
————————————————  —————————————————
            7534              99673
1 row selected. 

Code Listing 3: Using ROUND function to round numeric values to the left of the decimal point

SQL> select ROUND(7534.1238, -1), ROUND(99672.8591, -3)
  2    from dual;
ROUND(7534.1238,-1)  ROUND(99672.8591,-3)
———————————————————  ————————————————————
               7530                100000
1 row selected.

The TRUNC function returns a numeric value truncated to a certain number of decimal places. Like the ROUND function, it takes one required parameter and one optional parameter. The required parameter is the number to be truncated. The optional parameter is a positive or a negative integer. A positive integer specifies how many decimal places to truncate to. Listing 4 shows how the TRUNC function behaves when it is passed a positive value for the optional parameter. Note that the query simply truncates the returned values, leaving off any digits beyond two digits to the right of the decimal point.

Code Listing 4: Using TRUNC function to cut off digits to the right of the decimal point

SQL> select TRUNC(7534.1238, 2), TRUNC(99672.8591, 2)
  2    from dual;
TRUNC(7534.1238,2)  TRUNC(99672.8591,2)
——————————————————  ———————————————————
           7534.12             99672.85
1 row selected.

If you omit the optional parameter, the returned value will be truncated to zero decimal places, as shown in Listing 5. When you use a negative integer for the optional parameter, as shown in Listing 6, you are specifying how many digits to the left of the decimal point should be changed to 0 in the displayed results.

Code Listing 5: Using TRUNC function to truncate numeric values to whole numbers

SQL>  select TRUNC(7534.1238),  TRUNC(99672.8591)
  2     from dual;
TRUNC(7534.1238)  TRUNC(99672.8591)
————————————————  —————————————————
            7534              99672
1 row selected.

Code Listing 6: Using TRUNC function to truncate numeric values to the left of the decimal point

SQL> select TRUNC(7534.1238, -1), TRUNC(99672.8591, -3)
  2    from dual;
TRUNC(7534.1238,-1)  TRUNC(99672.8591,-3)
———————————————————  ————————————————————
               7530                 99000
1 row selected.

Code Listing 5: Using TRUNC function to truncate numeric values to whole numbers

SQL>  select TRUNC(7534.1238),  TRUNC(99672.8591)
  2     from dual;
TRUNC(7534.1238)  TRUNC(99672.8591)
————————————————  —————————————————
            7534              99672
1 row selected.

Similar to ROUND and TRUNC are the FLOOR and CEIL number functions. The FLOOR function determines the largest integer less than (or equal to) a particular numeric value. Conversely, the CEIL function determines the smallest integer greater than (or equal to) a particular numeric value. FLOOR and CEIL (unlike ROUND and TRUNC) do not take an optional parameter for precision, because their output is always an integer. When all four of these functions are applied to a positive number, as illustrated in Listing 7, FLOOR behaves similarly to TRUNC with no optional parameter specified, and CEIL behaves similarly to ROUND with no optional parameter specified. Note, however, that FLOOR behaves like ROUND and CEIL behaves like TRUNC when these functions are applied to a negative number.

Code Listing 7: Using and comparing ROUND, CEIL, TRUNC, and FLOOR functions

SQL> select ROUND(99672.8591), CEIL(99672.8591), TRUNC(99672.8591), FLOOR(99672.8591)
  2    from dual;
ROUND(99672.8591)  CEIL(99672.8591)  TRUNC(99672.8591)  FLOOR(99672.8591)
—————————————————  ————————————————  —————————————————  —————————————————
            99673             99673              99672              99672
1 row selected.

The four arithmetic operators (+, –, *, and /—for addition, subtraction, multiplication, and division) can be used in SQL statements and combined with one another and any of the number functions. Listing 8 shows a query from the EMPLOYEE table that reports each employee’s annual base salary, a calculated 3 percent bonus per salary value, and the weekly salary value (including bonus) for each employee.

Code Listing 8: Arithmetic operators in combination with the ROUND number function

SQL> select first_name, last_name, salary, salary*.03 "BONUS",
ROUND((salary/52)+((salary*.03)/52)) "Weekly Sal w/Bonus"
  2    from employee
  3  order by salary desc, last_name;
FIRST_NAME      LAST_NAME  SALARY   BONUS   Weekly Sal w/Bonus
——————————      —————————  ———————  ——————  ——————————————————
Emily           Eckhardt   100000   3000    1981
michael         peterson    90000   2700    1783
Donald          Newton      80000   2400    1585
Frances         Newton      75000   2250    1486
Matthew         Michaels    70000   2100    1387
mark            leblanc     65000   1950    1288
Roger           Friedli     60000   1800    1188
Betsy           James       60000   1800    1188
8 rows selected.

Two number functions, MOD and REMAINDER, can both be used to calculate the remainder of a value divided by another value. Both functions require two parameters: the value to be divided and the divisor. The MOD function uses the FLOOR function in its computation logic, and the REMAINDER function uses ROUND. For this reason, the values returned from the two functions can differ, as shown in Listing 9.

Code Listing 9: Differences between the MOD and REMAINDER function results

SQL> select MOD(49, 18) modulus, REMAINDER(49, 18) remaining
  2    from dual;
   MODULUS   REMAINING
——————————   —————————
        13          -5
1 row selected.

Recall that a null value in a table is the absence of a value. Null values cannot be compared with, or computed with, one another. However, you can substitute a non-null value for a NULL value by applying the NVL miscellaneous function to the NULL. The NVL function requires two input parameters: the expression (a column value, literal value, or computed result) to be tested for nullity and the expression to substitute for any NULL expressions in the results.

For example, Listing 10 shows a query that lists each employee alongside the EMPLOYEE ID value of that person’s manager. For the employees with no value for MANAGER—that is, those whose MANAGER values are NULL in the database—the results display 0 as the manager’s EMPLOYEE ID. This occurs because the query applies the NVL function to each MANAGER value, substituting 0 for any NULL.

Code Listing 10: Substitute a NULL value for MANAGER with a value of 0

SQL> select employee_id, last_name, first_name, NVL(manager, 0) manager
  2    from employee
  3  order by manager, last_name, first_name;
EMPLOYEE_ID   LAST_NAME     FIRST_NAME      MANAGER
———————————   ———————————   —————————————   —————————
         28   Eckhardt      Emily                   0
         37   Newton        Frances                 0
       6569   peterson      michael                 0
       6567   Friedli       Roger                  28
       6568   James         Betsy                  28
       7895   Michaels      Matthew                28
       1234   Newton        Donald                 28
       6570   leblanc       mark                 6569
8 rows selected.

As you can also see in Listing 10, the original value of the tested expression is returned if it is not NULL.

In the Listing 10 example, a returned value of 0 might not tell you as clearly as you’d like that certain employee records have no assigned manager value. Instead, you might prefer to return text that states this fact explicitly.

Listing 11 shows a query that tries to replace each NULL value with a more descriptive text literal. The query returns an error, however, because the NVL function requires the substitution value to be convertible to the datatype of the comparison value. However, you can obtain the textual output in a few ways. Listing 12 demonstrates one of them: the inclusion of a datatype conversion function, TO_CHAR. Datatype conversion functions will be discussed in detail in subsequent articles in this series.

Code Listing 11: Attempt to replace a returned NULL value with a text value

SQL>  select employee_id, last_name, first_name, NVL(manager,
'Has no manager') manager
  2    from employee
  3  order by manager, last_name, first_name;
select employee_id, last_name, first_name, NVL(manager, 'Has no manager') manager
                                                         *
ERROR at line 1:
ORA-01722: invalid number

SQL> select employee_id, last_name, first_name, NVL(TO_CHAR(manager),
'Has no manager') manager
  2   from employee
  3  order by manager, last_name, first_name;
EMPLOYEE_ID  LAST_NAME  FIRST_NAME  MANAGER
———————————  —————————  ——————————  ———————————————
       6567  Friedli    Roger                    28
       6568  James      Betsy                    28
       7895  Michaels   Matthew                  28
       1234  Newton     Donald                   28
       6570  leblanc    mark                   6569
         28  Eckhardt   Emily         Has no manager
         37  Newton     Frances       Has no manager
       6569  peterson   michael       Has no manager
8 rows selected.

Sometimes a simple substitution function such as NVL doesn’t provide all the choices you require. The DECODE function uses if-then-else logic to give you more than one possible substitution choice.

The syntax for the DECODE function starts with an input expression. It compares that expression with a comparison value. If the two values match (this is the “if-then” portion of the DECODE logic), the DECODE function returns the substitution value. If the two values do not match, the input expression will be compared with the next available comparison value. If another comparison value is not provided, the optional default substitution value (the “else” portion of the DECODE logic) will be returned. Listing 13 demonstrates the syntax for the DECODE function. It also demonstrates how DECODE functions can be nested inside one another.

Code Listing 13: DECODE substitution function

SQL> select employee_id, first_name, last_name, DECODE(manager, 28,
'Emily Eckhardt', 6569, 'Michael Peterson', DECODE(employee_id, 28,
'Is Emily', 6569, 'Is Michael', 'Neither Emily nor Michael')) manager
  2    from employee
  3  order by manager, last_name, first_name;
EMPLOYEE_ID   LAST_NAME     FIRST_NAME      MANAGER
———————————   ———————————   —————————————   ——————————————————
       6567   Roger         Friedli         Emily Eckhardt
       6568   Betsy         James           Emily Eckhardt
       7895   Matthew       Michaels        Emily Eckhardt
       1234   Donald        Newton          Emily Eckhardt
         28   Emily         Eckhardt        Is Emily
       6569   michael       peterson        Is Michael
       6570   mark          leblanc         Michael Peterson
         37   Frances       Newton          Neither Emily nor Michael
8 rows selected.

The query in Listing 13, like the one in Listing 12, substitutes a textual value for the actual MANAGER value for each employee record. Note, though, that with DECODE, you can repeat the test and substitute values—that is, repeat the if-then logic—as much as you require. Another difference from NVL is that DECODE can test for conditions other than nullity; for example, the query in Listing 13 tests whether a particular value exists.

If you do want to test for nullity with DECODE, you can write a query such as

SELECT DECODE(manager, NULL, 'Has no Manager', manager) FROM employee;

In this example, if the value obtained from the MANAGER column is NULL, the “Has no manager” string will be returned. Otherwise, the non-null manager value will be returned. You might be wondering why this statement does not return an error, given that the MANAGER value is of a different datatype than the string that would be returned if the MANAGER value were NULL. The reason is implicit datatype conversion. Oracle Database implicitly converts a number to a string in situations like this example. (It does not—and cannot—convert a string to a number.) However, it is not good practice to allow Oracle Database to perform implicit datatype conversions. If you need a datatype conversion, you should always perform a call to a datatype conversion function explicitly.

Although the DECODE function is more powerful than the NVL function, it cannot be (easily) used for comparisons other than equality (and inequality.) A searched CASE expression can not only be used in place of the DECODE function but can also be used more easily for greater-than or less-than comparisons.

Listing 14 shows a query that uses a searched CASE expression. As you can see, the searched CASE expression starts with the CASE keyword and ends with the END keyword. Each WHEN clause tests a condition. If a condition is true, the CASE expression will return the value specified in the associated THEN clause. Like the DECODE function’s ELSE condition, the default ELSE condition in the searched CASE expression is optional. CASE expressions can be used in WHERE clauses, as shown in Listing 15. They can even be nested, as shown in Listing 16.

Code Listing 14: Searched CASE expression in a less-than comparison

SQL> select employee_id, first_name, last_name, salary,
  2     CASE WHEN manager = 28 THEN 'Emily is the manager. No bonus this year.'
  3          WHEN salary  < 80000 THEN 'Bonus this year.'
  4          ELSE 'No bonus this year.'
  5     END "Bonus?"
  6   from employee
  7   order by last_name, first_name;
EMPLOYEE_ID  FIRST_NAME LAST_NAME  SALARY    Bonus?
———————————  —————————— —————————  ————————  ————————————————————————————————————————
         28  Emily      Eckhardt   100000    No bonus this year.
       6567  Roger      Friedli     60000    Emily is the manager. No bonus this year.
       6568  Betsy      James       60000    Emily is the manager. No bonus this year.
       7895  Matthew    Michaels    70000    Emily is the manager. No bonus this year.
       1234  Donald     Newton      80000    Emily is the manager. No bonus this year.
         37  Frances    Newton      75000    Bonus this year.
       6570  mark       leblanc     65000    Bonus this year.
       6569  michael    peterson    90000    No bonus this year.
8 rows selected.

Code Listing 15: Searched CASE expression in a WHERE clause

SQL> select employee_id, first_name, last_name, salary
  2    from employee
  3   where salary + CASE
  4            WHEN ROUND((salary/52)+((salary*.03)/52)) > 1500
  5            THEN 0
  6            WHEN ROUND((salary/52)+((salary*.03)/52)) < 1300
  7            THEN 500
  8         ELSE 200
  9         END > 75000
 10  order by last_name, first_name;
EMPLOYEE_ID    FIRST_NAME  LAST_NAME     SALARY
———————————    ——————————  ———————————   ———————————
         28    Emily       Eckhardt           100000
       1234    Donald      Newton              80000
         37    Frances     Newton              75000
       6569    michael     peterson            90000
4 rows selected.

Code Listing 16: Nested searched CASE expressions

SQL> select employee_id, first_name, last_name,
  2     CASE manager WHEN 28    THEN 'Emily Eckhardt'
  3                  WHEN 6569  THEN 'Michael Peterson'
  4     ELSE
  5        CASE employee_id WHEN 28    THEN 'Is Emily'
  6                         WHEN 6569  THEN 'Is Michael'
  7        ELSE 'Neither Emily nor Michael'
  8        END
  9     END manager
 10    from employee
 11  order by manager, last_name, first_name;
EMPLOYEE_ID  LAST_NAME     FIRST_NAME      MANAGER
———————————  ———————————   —————————————   ——————————————————
       6567  Roger         Friedli         Emily Eckhardt
       6568  Betsy         James           Emily Eckhardt
       7895  Matthew       Michaels        Emily Eckhardt
       1234  Donald        Newton          Emily Eckhardt
         28  Emily         Eckhardt        Is Emily
       6569  michael       peterson        Is Michael
       6570  mark          leblanc         Michael Peterson
         37  Frances       Newton          Neither Emily nor Michael
8 rows selected.

This article has shown you a few of the more common number functions and how you can use them to manipulate the way your data displays. You’ve seen how to round numeric data values up and down and how to truncate them. You now know how the ROUND and TRUNC number functions behave, in comparison to FLOOR and CEIL. You’ve also seen that the MOD and REMAINDER number functions can return different values because of the type of computation each one uses. Last but not least, you understand the power and differences of substitution functions such as NVL, DECODE, and searched CASE expressions.

This article has by no means provided an exhaustive list of the Oracle number and miscellaneous substitution functions. Review the documentation.

The next installment of SQL 101 will discuss date and datatype conversion functions.

READ SQL 101, Parts 1–6

READ more about relational database design and concepts
Oracle Database Concepts 11g Release 1 (11.2)
Oracle Database SQL Language Reference 11g Release 1 (11.1)
Oracle SQL Developer User’s Guide Release 3.1
number and substitution functions

DOWNLOAD the script for this article