Sample IDMS user written SQL scalar function

Document ID : KB000117769
Last Modified Date : 09/11/2018
Show Technical Document Details
Introduction:
This document provides an example of a user-written SQL scalar function, written in ADS.
Background:
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.
Instructions:
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
File Attachments:
KB000117769.zip