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

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

Get ready…. 

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).
CEILING-Scalar function
Image Courtesy|Stocksnap.io

The result can be null:

Let us see how it is…

if the argument is null, the result is the null value.

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)

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.