This document provides an example of a user-written SQL scalar function, written in ADS.
A common requirement is to convert a numeric value into a right-justified, zero padded, string representation of the same number.
It can be done using the provided CA IDMS scalar functions, but the SQL used becomes cumbersome, particularly if many columns are involved.
A trick to do this is as follows:
SELECT DEPT_HEAD_ID_0410,
RIGHT(CAST(DEPT_HEAD_ID_0410+10000 AS CHAR(5)),4) AS PADDED_NUM
FROM EMPNET.DEPARTMENT;
*+
*+ DEPT_HEAD_ID_0410 PADDED_NUM
*+ ----------------- ----------
*+ 321 0321
*+ 15 0015
*+ 11 0011
*+ 13 0013
*+ 3 0003
*+ 4 0004
*+ 7 0007
*+ 30 0030
*+ 349 0349
*+
*+ 9 rows processed