DB2: CEILING Scalar Function Example

The ‘CEILING’ is basically a built-in function and it is a part of a scalar function. The syntax and usage of this function explained here.

Get ready…. 

CEILING FUNCTION

The CEILING function returns the smallest integer value that is greater than or equal to the argument.

Syntax is

CEILING(numeric-expression)

The schema is SYSIBM.

3 Rules to Remember

1. The argument must be an expression that returns a value of any built-in numeric data type.
2. The argument can also be a character string or graphic string data type. The string input is implicitly cast to a numeric value of DECFLOAT(34).
3. The result of the function has the same data type and length attribute as the argument except that the scale is 0 if the argument is DECIMAL. For example, an argument with a data type of DECIMAL(5,5) results in DECIMAL(5,0).

The result can be null:

Let us see how it is…

if the argument is null, the result will be null.

Tip: CEIL can be specified as a synonym for CEILING.

Example 1:

The following statement shows the use of CEILING on positive and negative values:

SELECT CEILING(3.5), CEILING(3.1), CEILING(-3.1), CEILING(-3.5)
FROM SYSIBM.SYSDUMMY1;

This example returns: 04., 04., -03, -03.

Example 2:

Using sample table DSN8A10.EMP, find the highest monthly salary for all the employees. Round the result up to the next integer. The SALARY column has a decimal data type.

SELECT CEILING(MAX(SALARY)/12)
 FROM DSN8A10.EMP;

This example returns 04396. because the highest-paid employee is Christine Haas who earns $52750.00 per year. Her average monthly salary before applying the CEILING function is 4395.83.

(Ref:IBM)