• 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
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    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

  • 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