SQL- Functions and Return Code +100

Types of Functions.

• Column functions (aggregate function)
• 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 is you will not get +100, when data is not available. Only you will get NULL.

Scalar Function

Scalar functions are the ones you apply to a column value or expression.

Example:

POSSTR – returns the position of the first occurrence of the second argument within the first argument.

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