Sample IDMS user written SQL scalar function

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

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

 

Environment

CA IDMS, all supported releases.

Resolution

The code for this function is in the attached .zip file.
Add the contents of ocf.txt to OCF (you may need to change the name of the SQL schema SAMPSQL).
Add the contents of idd.txt to your dictionary with IDMSDDDL.
Run adsobcom.txt through ADSOBCOM to create the supporting dialog.
With this function in place, it becomes a simpler function call:

SELECT DEPT_HEAD_ID_0410,                             
       SAMPSQL.RJZP(DEPT_HEAD_ID_0410,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

 

Additional Information

Defining and Using Functions