Using SQL User Defined Functions for Sorts

February 6th, 2013

By using a UDF to manipulate your raw data during the SQL select process, you can;

• retrieve associated values that require complex calculations,
• perform data cross-referencing, and
• manipulate data beyond the basic build in SQL functions.

Using traditional methods without the utilization of SQL/UDF, several steps are involved.  They include copying required data to a work file, running business logic to perform calculations (e.g. inventory age), then sorting and outputting the data in the desired format.

Through the use of a UDF,  the inventory age calculation can occur during the data selection process, eliminating the overhead and processing associated with using temporary files for this task.  This results in your selected data being fully ready for output into the final format desired.

Additionally, a UDF offers the same benefits and advantages of service programs and APIs.   The most important benefit is developing standard business logic external to any specific program, allowing it to be re-used instead of repetitively developed and debugged!  If you need help deploying this strategy on the IBM i, let us know!