This post introduces SQL functions, explains how column and scalar functions work, and shows how to interpret SQL functions and return code +100 in practical examples.

Types of Functions.

• Column functions (aggregate functions)
• Scalar functions
• MQ Series built-in functions
• XML publishing built-in functions

Column Function

SELECT AVG(SAL) FROM MAX WHERE
EMP_NO = '1234'.

The interesting thing is you will not get +100 when data is not available. You will only get NULL. This is important because aggregate functions calculate a result from the values that are present, rather than treating missing data as a normal numeric value. A NULL result can therefore indicate that no usable value was available for the calculation.

Scalar Function

Scalar functions are the ones you apply to a column value or expression. Unlike column functions, which generally combine values from multiple rows, scalar functions work with one value at a time and return a transformed or calculated value for that row. They are useful for manipulating text, numbers, and dates directly within an SQL statement.

Example:

POSSTR – returns the position of the first occurrence of the second argument within the first argument. It searches the first string from left to right and returns the character position where the search string begins. If the search string cannot be found, the result indicates that there is no matching occurrence.

For example:
SELECT POSSTR(‘DATABASE ADMINISTRATION’, ‘ADMIN’)
FROM SYSIBM.SYSDUMMY1;

This SQL statement returns the value 10; the value ‘ADMIN’ first appears in the 10th position.

Check here for more details on functions. DB2 SQL functions. I will cover some information on UDF (user-defined functions). Together, these built-in and user-defined capabilities make SQL expressions more useful and reduce the need to move data into a separate program for basic processing. Choosing the appropriate function can also make a query clearer and easier to maintain.