• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • SQL Functions You Didn’t Know You Had, Part 1

    October 22, 2014 Ted Holt

    What if I told you that you may have some potential powerful SQL functions on your system that you are not aware of? What if I told you that I have no idea what those SQL functions are named? What if I told you that you could easily use existing RPG routines in SQL queries? Would you be interested?

    (Take a tip from the Little Rascals (AKA Our Gang) and say, “And how!”)

    It’s easy to make SQL functions out of the subprocedures in service programs. To illustrate, here’s the source code for an RPG module from which a service program is built.

    ctl-opt  nomain;
    
    /copy prototypes,addr
    
    dcl-proc  GetCity     export;
    
       dcl-pi  *n     varchar(24);
          inAddress   varchar(48)   const;
       end-pi;
    
       return %subst(inAddress: 1: %scan (',': inAddress) - 1);
    
    end-proc  GetCity;
    
    dcl-proc  GetState    export;
    
       dcl-pi  *n     char(2);
          inAddress   varchar(48)   const;
       end-pi;
    
       return %subst(inAddress: %scan (',': inAddress) + 2: 2);
    
    end-proc  GetState;
    
    dcl-proc  GetZip      export;
    
       dcl-pi  *n     varchar(10);
          inAddress   varchar(48)   const;
       end-pi;
    
       return %subst(inAddress: %scan (',': inAddress) + 5);
    
    end-proc  GetZip;
    

    You don’t have to use free-form RPG, as I did when putting together this illustration. Fixed-format RPG works just as well.

    I based this illustrative module on a project on which I worked recently. I had to work with data in which city, state, postal code, and country name were stored together as one value. I had to come up with a way to separate the parts of the address. This example is a vastly simplified version that extracts city, state, and ZIP code (the U. S. postal code). In this version, I assume that the city is followed by a comma and space, then the state, one space, and finally the ZIP code.

    I create the module.

    CRTRPGMOD MODULE(MYLIB/ADDR) SRCFILE(MYLIB/QRPGLESRC)
    

    I create a service program from the module.

    CRTSRVPGM SRVPGM(MYLIB/ADDR) EXPORT(*ALL)
    

    Now my programs can bind to the service program to use the GetCity, GetState, and GetZip subprocedures. Wonderful! This is what ILE is all about.

    Let’s take it a step further. By creating SQL functions over those subprocedures, I can use this same code in SQL queries. Here is the command to create the GetCity function. (I’ll leave it to you to create the GetState and GetZip functions if you’re interested.)

    create or replace function GetCity
    (inString varchar(48))
    returns varchar(24)
    language rpgle
    parameter style general
    deterministic
    no sql
    returns null on null input
    no external action
    not fenced
    no final call
    allow parallel
    no scratchpad
    external name 'MYLIB/ADDR(GETCITY)'
    

    Here’s a query that uses these new SQL functions.

    select key, CityStateZip, GetCity(CityStateZip),
                GetState(CityStateZip), GetZip(CityStateZip)
    from BigData
    

    And here are the results:

    KEY CITYSTATEZIP                  GETCITY        GETSTATE  GETZIP
    === ============================= ============== ========  ==========
      1 Sand Francisco, MS 38888-1492 Sand Francisco    MS     38888-1492
      2 Lost Angeles, TX 77777        Lost Angeles      TX     77777
      3 New Yolk, CA 99999-4949       New Yolk          CA     99999-4949
    

    Now think about the service programs in your shop. Each exported subprocedure that returns a value is a potential SQL function. Isn’t that great!? (Take a tip from the Little Rascals and exclaim, “You’re tellin’ me!”)



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Rocket Software

    Two Steps Forward, No Steps Back

    For over 35 years, Rocket Software’s solutions have empowered businesses to modernize their infrastructure, unlock data value, and drive transformation – all while ensuring modernization without disruption.

    LEARN MORE

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    BCD:  On-Demand Webinar: Deliver First-Rate IBM i Mobile Applications Quickly.
    COMMON:  Join us at the COMMON 2014 Fall Conference & Expo in Indianapolis, Oct 27-29
    LANSA:  Getting ready to build a mobile app? Webinar: October 21, 11 am CT / 1 pm ET

    More IT Jungle Resources:

    System i PTF Guide: Weekly PTF Updates
    IBM i Events Calendar: National Conferences, Local Events, and Webinars
    Breaking News: News Hot Off The Press
    TPM @ EnterpriseTech: High Performance Computing Industry News From ITJ EIC Timothy Prickett Morgan

    iBelieve Tour Returns to Europe What The IBM Chip Biz Selloff Means To IBM i Shops

    Leave a Reply Cancel reply

Volume 14, Number 24 -- October 22, 2014
THIS ISSUE SPONSORED BY:

CCSS
WorksRight Software
Shield Advanced Solutions

Table of Contents

  • SQL Functions You Didn’t Know You Had, Part 1
  • Paging Cursors
  • The IBM i *LOOPBACK Interface Problem

Content archive

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

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

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 © 2025 IT Jungle