• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: Alternate SQL Row-Selection Criteria Revisited Revisited

    September 27, 2021 Ted Holt

    I am not a “do as I say and not as I do” kind of person. After I wrote the article Guru: Alternate SQL Row-Selection Criteria Revisited, I got serious about using dynamic SQL in my RPG programs instead of enabling and disabling logical expressions in the WHERE clause. I’m reliving the days when OPNQRYF was my best friend, trying to make apostrophes play nice with string concatenation.

    Overall, I’m happy with the change. When I have converted the switch-laden version to dynamic SQL, I have seen a noticeable performance improvement in some cases, and at least a slight performance in most cases. In some cases, it doesn’t make any difference, even though Visual Explain shows fewer steps to retrieve the data.

    This story contains code, which you can download here.

    One thing is an especially annoying nuisance, namely having to code multiple OPEN cursor statements to accommodate multiple parameter lists. If you look at the aforementioned article, you’ll see this in the last code snippet:

    select;
       when SelectByClass = '1';
          exec sql  open Inp using :iClass;
       when SelectByZip = '1';
          exec sql  open Inp using :iFromZip, :iThruZip;
       other;
          exec sql  open Inp;
    endsl;
    

    I had to use multiple open statements to accommodate different numbers of parameter markers, the question marks that are used in dynamic SQL as place holders for parameter values. The more options you give the user, the more open statements you end up having to use.

    However, IBM thoughtfully came up with a way to use one open statement regardless of the number of parameter markers in a query string. It’s easy and I like it a lot.

    One single OPEN statement takes the place of the three OPEN statements shown above.

    exec sql  open Inp using subset :iClass    :Indic_Class,
                                    :iFromZip  :Indic_Zip,
                                    :iThruZip  :Indic_Zip;
    

    Notice the word SUBSET after USING. This tells SQL that you may or may not use all of the parameters. In fact, you may not use any of them at all. In this case, I have three potential parameter markers in the query to represent class, from ZIP code, and thru ZIP code.

    After each parameter, I placed an indicator variable to tell SQL whether that parameter is being used or not. An indicator variable is a five-digit integer value.

    dcl-s  Indic_Zip         int(5)     inz(Unassigned);
    dcl-s  Indic_Class       int(5)     inz(Unassigned);
    
    dcl-c  Assigned          const(*zero);
    dcl-c  Unassigned        const(-7);
    
    

    In this example, I have defined two indicator variables — one for class and one for the two ZIP code fields.

    Each indicator variable should be set to one of two values. Zero means that the variable is to be used and negative seven means the variable is to be ignored. I defined two numeric constants in an attempt to make the code more understandable.

    The other thing to do is to make sure that the indicator variables are consistent with the query string.

    Stmt = 'select cusnbr, cusname, cuszip, cusclass from customers';
    
    select;
       when iClass <> *blanks;
          Stmt += ' where cusclass = ?';
          Indic_Class = Assigned;
       when iFromZip <> *blanks;
          Stmt += ' where cuszip between ? and ?';
          Indic_Zip = Assigned;
    endsl;
    

    In this example, I allow the user to retrieve all rows, only rows for a certain class, or all rows within a range of ZIP codes,

    Of course, there’s no reason I couldn’t allow the user to select for both class and range of ZIP codes.

    dcl-s  Indic_From_Zip    int(5)     inz(Unassigned);
    dcl-s  Indic_Thru_Zip    int(5)     inz(Unassigned);
    dcl-s  Indic_Class       int(5)     inz(Unassigned);
    
    dcl-c  Assigned          const(*zero);
    dcl-c  Unassigned        const(-7);
    
    dcl-s  Stmt              varchar( 512);
    dcl-s  Where             varchar( 128);
    dcl-s  And               char   (   5);
    
    Stmt = 'select cusnbr, cusname, cuszip, cusclass from customers';
    
    if iClass <> *blanks;
       Where = ' (cusclass = ?) ';
       Indic_Class = Assigned;
    endif;
    
    if iFromZip <> *blanks;
       Indic_From_Zip = Assigned;
       if Where <> *blanks;
          And = ' and ';
       endif;
       if iThruZip <> *blanks
       and iThruZip <> iFromZip;
          Where += (And + ' (cuszip between ? and ?)');
          Indic_Thru_Zip = Assigned;
       else;
          Where += (And + ' (cuszip = ?)');
       endif;
    endif;
    
    if Where <> *blanks;
       Stmt += ' Where ' + Where;
    endif;
    
    
    exec sql  open Inp using subset :iClass    :Indic_Class,
                                    :iFromZip  :Indic_From_Zip,
                                    :iThruZip  :Indic_Thru_Zip;
    
    

    I’ve put the source code for these examples in the downloadable code in case it may be of use to you.

    I could avoid the nuisance of multiple opens by concatenating the values themselves into the command strings, which would eliminate the need for parameter markers. The likelihood of one of my programs being targeted for SQL injection is slim. Then again, there’s a lot of old code that’s still running and shows no sign of going away any time soon.

    Besides, I would lose the advantage of preparing a statement once, then opening it with different values as needed.

    RELATED STORIES

    Guru: Alternate SQL Row-Selection Criteria Revisited

    The Dangers of Dynamic SQL

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, FHG, Four Hundred Guru, IBM i, OPNQRYF, RPG, SQL

    Sponsored by
    ARCAD Software

    [On-Demand Webinar] Understanding Value Stream Management (VSM) in an IBM i World

    Value Stream Management (VSM) has become one of the hottest topics in the move to DevOps over the last couple of years.  In this webinar we will look at what’s driving this adoption and why VSM is so vital to DevOps performance and to the IBM i world in particular.

    Value Stream Management is a management technique and practice enabling organizations to analyze and evaluate their end-to-end development process. VSM techniques extract key metrics from your system to help identify bottlenecks, continuously improve and optimize your DevOps flow. As organizations move toward a more agile world, VSM helps you accelerate value delivery and is business critical to staying competitive.

    In our Webinar, our DevOps experts will answer your questions around VSM on IBM i, including:

    • What is the difference between Value Stream Mapping and Value Stream Management?
    • Why VSM is so critical to drive improvements in DevOps processes?
    • How VSM techniques facilitate data-driven decisions?
    • How to identify bottlenecks in software development and delivery?
    • How your IBM i development plays a key role in the VSM ecosystem?
    • How ARCAD VSM and Application Intelligence Dashboards capability can help you evaluate and enhance your current IBM i assets?

    Learn how VSM techniques deliver a 360 degree view of your existing DevOps processes to help you optimize and drive efficiencies right across your IT organization.

    Watch Now!

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    IBM Adds Public Cloud Targets For IBM i Cloud Backup Fresche Takes On New RPG Development And Modernization With X-Elevate

    One thought on “Guru: Alternate SQL Row-Selection Criteria Revisited Revisited”

    • Dave Weissman says:
      June 9, 2022 at 4:05 pm

      Great article – thanks for the writeup and for sharing this technique. I implemented it and it works nicely!

      One question – why did you set the “unassigned” parameter marker to -7. Could it really be any negative number, or did the -7 have a specific purpose/meaning?

      Reply

    Leave a Reply Cancel reply

TFH Volume: 31 Issue: 62

This Issue Sponsored By

  • Fresche Solutions
  • ProData
  • ARCAD Software
  • Raz-Lee Security
  • WorksRight Software

Table of Contents

  • What IBM i Shops Want From Cloud, And How To Do It Right
  • Fresche Takes On New RPG Development And Modernization With X-Elevate
  • Guru: Alternate SQL Row-Selection Criteria Revisited Revisited
  • IBM Adds Public Cloud Targets For IBM i Cloud Backup
  • The Big Iron Customers That The Power E1080 Is Aimed At

Content archive

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

Recent Posts

  • IBM Mulls Using DataMigrator as Cloud Warehouse Pipeline
  • PowerTech AV Automatically Detects Ransomware Activity
  • Infor Puts CM3 Project On Hold
  • Four Hundred Monitor, June 29
  • IBM i PTF Guide, Volume 24, Number 26
  • Guild Mortgage Takes The 20-Year Option For Modernization
  • IBM i Licensing, Part 3: Can The Hardware Bundle Be Cheaper Than A Smartphone?
  • Guru: The Finer Points of Exit Points
  • Big Blue Tweaks IBM i Pricing Ahead Of Subscription Model
  • We Still Want IBM i On The Impending Power E1050

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.