Single-row Functions and Operations on Numerical Data

In Oracle Database, a function is a set of SQL expressions that takes arguments, performs some pre-defined operations, and returns an output. Specifically, a single-row function, or scalar function, returns one output for each one row processed. 

Depending on the data types to which they are applied, Oracle provides different types of single-row functions. Character functions take text values stored in each row in a table and return manipulated strings. Date functions work with date values in each row and return processed dates. In this blog post, our focus will be on exploring single-row functions applicable on numerical values.

The DUAL Table

Before we get started with single-row functions in Oracle SQL, let's just have a very simple statement that prints 'Hello, World!':

In the figure shown above, Oracle throws an error and says, "FROM keyword not found where expected." This is because Oracle requires you to specify the table from which you want to retrieve data in a SELECT statement.

To make this statement work you should add a FROM clause with a dummy table named DUAL:

In Oracle SQL, the DUAL table is a one-row table that serves as a convenient placeholder when you need to execute a SELECT statement without intending to retrieve specific data. 

Numerical Functions and Operations

On numeric data values, you can apply some arithmetic operations. For example:

Other than the operations described in the figure above, here are some common single-row functions that can be applied on numerical values and perform data manipulations in Oracle SQL:

• ABS(n): Returns the absolute value of a number. E.g., ABS(-5.3) = 5.3
• SIGN(n): Returns the sign of a number, indicating whether it's positive, negative, or zero. E.g., SIGN(-15) = -1, SIGN(0) = 0, SIGN(23.5) = 1
• CEIL(n): Returns the smallest whole number that is greater than or equal to a number (rounds up). E.g., CEIL(4.2) = 5, CEIL(-2.9) = -1
• FLOOR(n): Returns the largest whole number that is less than or equal to a number (rounds down). E.g., FLOOR(4.8) = 4
• ROUND(n, i): Rounds a number (n) to a specified number of decimal places (i). E.g., ROUND(3.14159, 2) = 3.14
• TRUNC(n, i): Truncates a number (n) to a specified number of decimal places (i), removing digits beyond the decimal point without rounding. E.g., TRUNC(2.71828, 2) = 2.71
• REMAINDER(n1, n2): Returns the remainder of n1 divided by n2. E.g., REMAINDER(17, 4) = 1
• MOD(n1, n2): Returns the modulo of n1 divided by n2. E.g., MOD(17, 4) = 1

While both REMAINDER and MOD functions return the remainder of a division in Oracle SQL, it is worth noting that they differ in their behavior in some cases. For example:

This happens because the REMAINDER(n1, n2) identifies the multiple of n2 that is nearest to n1 and returns the difference between those two values. In the example shown above, n1 is 11 and n2 is 4. In this case, multiple of 4 nearest to 11 is 12. Thus, the output of the function is 11 minus 12, which is negative one.

On the other hand, in the same example, the MOD(11,4) returned the non-negative remainder. This is because MOD(n1, n2) uses FLOOR instead of ROUND in its equation. So, in this example, multiple of 4 that is not greater than 11 is 8. Thus, the returning output of the expression MOD(11,4) is 11 minus 8, which is three. 

This property of MOD makes the resulting value has the same sign as the divisor. For example:

In the example shown above, the nearest multiple of 4 to -11 is -12. Thus, REMAINDER(-11,4) = 1, as negative eleven minus negative twelve is evaluated to be one. However, when I applied MOD function to the same arguments, it returned -3. This is because -11 divided by 4 has quotient of -2 and remainder of -3.