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

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.