# 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)

Posted in SQL

## Author: Srini

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

## One thought

Comments are closed.