• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Dynamic Lists In Static SQL Queries

    March 31, 2015 Ted Holt

    Hey, Ted:

    We have an SQL query that can take from one to 13 two-byte codes that become part of an IN clause for row selection. We have no idea which codes they will need to see beforehand; it’s an interactive thing. We are using dynamic SQL. I would like to find a static solution. Any suggestions?

    –Harold

    Sure, Harold. I do this sort of thing from time to time, and I use static SQL to do it.

    Let’s say those two-byte codes are state abbreviations, like the state abbreviations we use in the USA. You probably have a form or display into which the user keys the codes, but I’m going to use a command interface for an example.

    Here’s command AR102, which allows the entry of up to 12 state abbreviations.

    CMD        PROMPT('Print a Customer Report')         
    PARM       KWD(STATE) TYPE(*CHAR) LEN(2) +           
                 SNGVAL((*ALL 'XX')) MAX(12) EXPR(*YES) +
                 PROMPT('State abbreviation(s)')         
    

    We can run queries using commands like these:

    AR102 STATE(CA CO)
    AR102 STATE(TX MN VT)
    AR102 STATE(CA GA NM VT MN CO)
    

    This command runs RPG program AR102R, which uses the dynamic approach you’re using.

    H dftactgrp(*no) actgrp('AR')
    
    Far102p    o    e             printer usropn
    
    D AR102R          pr                  extpgm('AR102R')
    D  inStateList                  26a   const
    D AR102R          pi
    D  inStateList                  26a   const
    
    D String          s            256a   varying template
    
    D cSqlEof         c                   const('02000')
    D Command         s                   like(String)
    D StateList       s                   like(String)
    
    D CustRec       e ds                  extname('QCUSTCDT')
    
       *inlr = *on;
       BuildListOfStates ();
       BuildCursor ();
       BuildReport ();
       return;
    
    P BuildListOfStates...
    P                 b
    
    D Size            ds             2
    D  ListSize                      5i 0
    
    D Offset          s              3u 0
    D Ndx             s              3u 0
    D Quote           c                   const('''')
    D Sep             s              1a   varying
    
        Size = %subst(inStateList:1:2);
        Offset = 1;
    
        StateList = '(';
        For Ndx = 1 to ListSize;
           Offset += 2;
           StateList = StateList + Sep + Quote
                           + %subst(inStateList:Offset:2) + Quote;
           Sep = ',';
        endfor;
        StateList = StateList + ')';
    P                 e
    
    P BuildCursor...
    P                 b
       Command = 'select * from qcustcdt ' +
                 ' where state in ' + StateList +
                 ' order by state, cusnum';
       exec sql  declare c1 cursor for p1;
       exec sql  prepare p1 from :Command;
    P                 e
    
    P BuildReport...
    P                 b
       exec sql  open c1;
       open ar102p;
       dow '1';
          exec sql  fetch c1 into :CustRec;
          if sqlstate >= cSqlEof;
             leave;
          endif;
          write detail;
       enddo;
       close ar102p;
       exec sql  close c1;
    P                 e
    

    The command sends the list of codes to the RPG program as a string. The first two bytes are a signed integer that tells how many values there are in the list. The state abbreviations follow. The BuildListOfStates subprocedure converts the list into the form of an IN clause.

    If I run this command:

    AR102 STATE(CA CO MN)
    

    The RPG program receives this:

    **CACOMN
    

    The two asterisks are a lie. What’s really there is the integer 3 (hexadecimal 0003), which I have no way of accurately representing in text. The RPG program generates this:

    ('CA','CO','MN')
    

    The entire SQL command looks like this:

    select * from qcustcdt
     where state in ('CA','CO','MN') 
     order by state, cusnum
    

    AR102R reads file QCUSTCDT, which is in library QIWS, selecting the rows from the indicated states. This works fine. There’s nothing wrong with it.

    However, if you prefer static SQL, as I do, you can’t build the IN clause dynamically. Here’s a technique that works for me.

    First, I create a temporary table in which to load the codes. One code, one row. Then I refer to that table in the IN clause. Here’s the revised program.

    H dftactgrp(*no) actgrp('AR')
    
    Far102p    o    e             printer usropn
    
    D AR102R          pr                  extpgm('AR102R')
    D  inStateList                  26a   const
    D AR102R          pi
    D  inStateList                  26a   const
    
    D String          s            256a   varying template
    
    D cSqlEof         c                   const('02000')
    
    D CustRec       e ds                  extname('QCUSTCDT')
    
       *inlr = *on;
       BuildListOfStates ();
       BuildCursor ();
       BuildReport ();
       return;
    
    P BuildListOfStates...
    P                 b
    
    D Size            ds             2
    D  ListSize                      5i 0
    
    D Offset          s              3u 0
    D Ndx             s              3u 0
    D State           s              2a
    
        exec sql  declare global temporary table List
           (State char(2))
           with replace;
    
        Size = %subst(inStateList:1:2);
        Offset = 1;
    
        For Ndx = 1 to ListSize;
           Offset += 2;
           State = %subst(inStateList:Offset:2);
           exec sql  insert into session.List values (:State);
        endfor;
    P                 e
    
    P BuildCursor...
    P                 b
       exec sql  declare c1 cursor for
                  select * from qcustcdt
                   where state in (select State from session.List)
                   order by state, cusnum;
    P                 e
    
    P BuildReport...
    P                 b
       exec sql  open c1;
       open ar102p;
       dow '1';
          exec sql  fetch c1 into :CustRec;
          if sqlstate >= cSqlEof;
             leave;
          endif;
          write detail;
       enddo;
       close ar102p;
       exec sql  close c1;
    P                 e
    

    Notice how the BuildListOfStates and BuildCursor subprocedures have changed. BuildListOfStates creates temporary table LIST in QTEMP and writes each code into it. BuildCursor refers to LIST in the IN clause.

    For completeness, here’s the printer file definition.

    A                                      REF(QCUSTCDT)
    A          R DETAIL                    SPACEB(1)
    A            CUSNUM    R              1
    A            LSTNAM    R             +1
    A            INIT      R             +1
    A            STREET    R             +1
    A            CITY      R             +1
    A            STATE     R             +1
    A            BALDUE    R             +1EDTCDE(1)
    

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Focal Point Solutions Group

    CLOUD SOLUTIONS

    From Production, Test and Development, Disaster and Backup environments, as well as hosting customer-owned servers, we offer a variety of Cloud Solutions to accommodate all sizes of business and industry. FPSG is in a unique position to provide services for multinational corporations and SMBs with data centers located throughout North America and Europe.

    Does your IBM midrange, AIX, iSeries, Intel, or Linux environment need an improved Cloud strategy?

    EXPLORE OUR CUSTOM SOLUTIONS

    • Production Solutions
    • Disaster Recovery
    • Backup and Recovery Services
    • Data Centers
    • Security & Compliance Services
    • Application Hosting

    MANAGED SERVICES

    Focal Point offers a variety of custom-managed services from daily operations management and monitoring to high availability, backup, and disaster recovery support services. If your enterprise requirements reside in IBM midrange, AIX, iSeries, Intel, or Linux, FPSG can help support your team with our managed services experts. No User downtime for production backups.

    • System Monitoring
    • Server & SAN
    • High Availability/Disaster Recovery Monitoring
    • Managed Backup Services
    • Security Administration & Monitoring
    • Cloud Environment Monitoring

    Our experts combine decades of experience with industry-leading innovation to customize effective solutions for your organization, budget, and infrastructure.

    It’s not uncommon for organizations to miss important requirements when implementing and planning for new technologies. Our Managed Technical Services ensure total compliance, with no compromise or loss of data. Our skilled specialists will design and execute a non-disruptive and comprehensive solution, allowing your IT team to concentrate on the day-to-day activities – resulting in a more agile and cost-effective infrastructure.

    Watch our IntellaFLASH™ Video to learn more

    Let’s Discuss Your Custom Solution Needs

    Follow us on LinkedIn

    focalpointsg.com | 813.513.7402

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    LaserVault:  Webinar - IBM i: Intro to Tapeless Backups. April 9
    Chrono-Logic:  Deploy automatically to multiple IBM i and Windows servers with a single click!!
    COMMON:  2015 Annual Meeting & Expo, April 26 - 29, at the Disneyland® Resort in Anaheim, California

    Reader Feedback On What’s Up In The IBM i Marketplace . . . Don’t Miss the IBM i Marketplace Webcast . . . COMMON Is Coming, Linux and VIOS Get Top Billing Zend And BCD Get A Little Closer For PHP

    Leave a Reply Cancel reply

Volume 15, Number 06 -- March 31, 2015
THIS ISSUE SPONSORED BY:

WorksRight Software
HelpSystems
LaserVault

Table of Contents

  • Handling Constraints Revisited
  • Dynamic Lists In Static SQL Queries
  • RubyGems Are The Foundation Of Success

Content archive

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

Recent Posts

  • DRV Brings More Automation to IBM i Message Monitoring
  • Managed Cloud Saves Money By Cutting System And People Overprovisioning
  • Multiple Security Vulnerabilities Patched on IBM i
  • Four Hundred Monitor, June 22
  • IBM i PTF Guide, Volume 24, Number 25
  • Plotting A Middle Age Career Change To IBM i
  • What Is Code Transformation Even?
  • Guru: The CALL I’ve Been Waiting For
  • A Frank Solstice
  • The Inevitable Wave Of Power9 Withdrawals Begins

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

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.