Delimited List Processing in SQL
March 24, 2004 Michael Sansoterra
[The code for this article is available for download.]
I recently worked on the database side of a Web project for which the Web developers needed to pass several item numbers to an iSeries for processing. Traditional methods would have dictated these items be inserted into a temporary or transaction file, then converted and processed as an XML file or passed individually to a stored procedure for processing.
An alternative that is fast and painless (provided the related item information is minimal) is to make a single call to a stored procedure and to pass the item numbers in a delimited list as a single parameter (for instance, “ITEM1,ITEM2,ITEM3”). This approach is great in terms of performance but is awkward to process in SQL.
You can remove the awkwardness by creating a user-defined table function to transform the delimited list into a set of rows. For the record, a table function (available in V5R2) is a program designed to supply data to SQL in a tabular format. Consider this table function:
Create Function xxxxx/ListUDTF (parmList VarChar(24576), parmDelim VarChar(10), parmMaxItems Integer) Returns Table(ItemNo Integer, Item VarChar(128)) External Name 'xxxxx/LISTUDTFR(PROCESSLIST)' Language RPGLE Disallow Parallel No SQL Parameter Style DB2SQL Deterministic
The function accepts three parameters: a delimited list, a delimiter, and a maximum number of items to return. The RPG program LISTUDTFR transforms the list into rows such that the following statement:
Select * From Table(ListUDTF('ITEM1,ITEM2,ITEM3',',',9999)) As List
Returns the following table:
The function can then be used in a stored procedure to create multiple order lines based on a delimited list of items and order quantities, such that the Web developers only call the procedure once:
Create Procedure spAddWebOrder (parmOrderID Integer, parmItems VarChar(1600), parmQtys VarChar(1600)) Language SQL Modifies SQL Data Set Option Commit=*None Begin Insert Into OrderLines Select parmOrderID, Items.Item, Cast(Qtys.Item As Integer) As Qty, Part.ListPrice From Table(ListUDTF(parmItems,',',9999)) As Items Join Table(ListUDTF(parmQtys,',',9999)) As Qtys On Qtys.ItemNo=Items.ItemNo Join PartMaster As Part On Part.PartNo=Items.Item End
As you can see, the possibilities for delimited list processing are enormous. To tinker with this table function yourself, download and compile RPG program ListUDTFR. Follow the instructions in the header for registering the function (using the CREATE FUNCTION statement).
In terms of programming, an external table function (written in a high-level language) is similar to an external scalar function, except that it can return multiple column values and multiple rows.
When invoked in an SQL statement, SQL will call the function’s program with a pre-defined call type (OPEN, FETCH, CLOSE.) When a fetch is requested, the program is expected to fill in the output parameters to build one row of data and exit. This process is repeated until the program sets the SQLState parameter to 02000, which indicates that it has no more data to return.
Further, the table function is given opportunity to do initialization and cleanup work (during the OPEN and CLOSE calls), which occur once at the beginning and end of each function invocation. During the open, any initialization work should be performed. During the close, all cleanup work should be performed and, in the case of an RPG program, *INLR should be set on to end the program completely.
For help understanding how to build SQL function parameter lists, see the article “Scribble on SQL’s Scratchpad.” For more information on creating table functions, see the SQL Reference (in PDF format) and SQL Programming Concepts (also in PDF format) manuals.
Michael Sansoterra is a programmer/analyst for SilverLake Resources, an IT services firm based in Grand Rapids, Michigan. E-mail: firstname.lastname@example.org