‘CEILING’ scalar function in DB2, the result can be NULL

CEILING
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.

  • The argument must be an expression that returns a value of any built-in numeric data type.
  • 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).
  • 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; if the argument is null, the result is the null value.

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

Advertisements

One thought on “‘CEILING’ scalar function in DB2, the result can be NULL

  1. Pingback: The ultimate notes on SQL functions to make you stronger – Srinimf

Comments are closed.