• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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:

    ItemNo Item
    1 ITEM1
    2 ITEM2
    3 ITEM3

    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: msansoterra@silver-lake.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Chrono-Logic Offers Cross Reference Tool for LANSA Early Adopters Prep for New OS/400, iSeries, and Logical Partitions

    Leave a Reply Cancel reply

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • IBM i Has a Future ‘If Kept Up To Date,’ IDC Says
  • When You Need Us, We Are Ready To Do Grunt Work
  • Generative AI: Coming to an ERP Near You
  • Four Hundred Monitor, March 22
  • IBM i PTF Guide, Volume 25, Number 12
  • Unattended IBM i Operations Continue Upward Climb
  • VS Code Is The Full Stack IDE For IBM i
  • Domino Runs on IBM i 7.5, But HCL Still Working on Power10
  • Four Hundred Monitor, March 6
  • IBM i PTF Guide, Volume 25, Number 11

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2023 IT Jungle