• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: SELECT INTO And Arrays

    March 8, 2021 Ted Holt

    I got egg on my face again. I told a couple of colleagues that they could use the SELECT INTO statement to load multiple rows into an array data structure in an RPG program. Boy, was I wrong! I had confused SELECT INTO with the FETCH statement, of course, which retrieves data over which a cursor has been declared.

    But the matter continued to nag me. I much like the simplicity of SELECT INTO. There’s no cursor to declare, open, fetch from and close, the same reason I like the FOR loop in SQL PL. It seemed (and continues to seem) unjust to me that I can retrieve one data value into a scalar variable without a cursor, but I can’t put two or more values into an array without a cursor. What’s so hard about putting the first row into the first element, the second row into the second element, and so on? A caveman could do it.

    Well, as the old saying goes, where there’s a will, there’s a lazy programmer. It turns out it’s possible to load an array with SELECT INTO. You just have to be sneaky about it.

    To illustrate, I’ll use physical file QCUSTCDT, which you can find on your system in the QIWS library. Let’s say we need the account numbers of the customers who owe us at least 100 units of currency. A simple SELECT yields one row per customer. We can use the LISTAGG function to combine all the customer account numbers into one long string. Since the account number is zoned decimal, the DIGITS function converts it to character data.

    select listagg(digits(c.CusNum))
     within group (order by c.CusNum) as List
       from qcustcdt as c
      where c.BalDue >= 100;
    

    LISTAGG converts a set of rows into a single value. The first parameter is an expression that specifies which data is to be retrieved. The second parameter is the value to separate the values. I omitted the second parameter since I didn’t want any separators. If I had specified the second parameter, I would have used two single quotes with nothing between them, like this.

    select listagg(digits(c.CusNum),'')
    

    Sorting works a little differently. Here the ORDER BY is placed into the WITHIN GROUP clause so that LISTAGG can take care of sorting the data. In this example, data structure CustInfo defines the same portion of memory two ways — as scalar value LIST and as array ACCOUNT.

    Here’s the result set:

    192837392859475938583990839283938485
    

    How do we turn that into in array? With a data structure!

    **free
    dcl-ds CustInfo     qualified;
       List         char(78)             pos(1);
       Account      char( 6)   dim(13)   pos(1);
    end-ds CustInfo;
    
    dcl-s  CX               uns( 5);
    
    exec sql
       select listagg(digits(c.cusnum),'')
       within group (order by c.cusnum) as List
         into :CustInfo.List
         from qcustcdt as c
        where c.BalDue >= 100;
    
    CX = 1;
    dow CustInfo.Account (CX) <> *blanks;
       dsply CustInfo.Account (CX);
       CX += 1;
    enddo;
    

    Here’s another example. Let’s get a list of all states in which we have customers.

    select listagg(distinct c.State,'')
     within group (order by c.State) as List
       from qcustcdt as c;
    

    I included this example for one reason only: to show you that you can use the DISTINCT keyword to eliminate duplicate values from the returned result.

    CACOGAMNNYTXVTWY
    

    You don’t have to put the results into an array of fixed-length values. For instance, you might prefer the values to be separated by a comma or some other value. Specify the separator string in the second parameter of LISTAGG. This statement . . .

    select listagg(distinct trim(c.City) concat ',' concat trim(c.State),';')
     within group (order by c.City, c.State) as List
       from qcustcdt as c;
    

    . . . yields this string . . .

    Broton,VT;Casper,WY;Clay,NY;Dallas,TX;Denver,CO;Hector,NY;Helen,GA;Isle,MN;Sutter,CA
    

    This technique would be handy in cases where the retrieved values were of different lengths, i.e. VARCHAR columns or TRIM’ed data. You can use RPG’s %SCAN function within a loop to extract the values.

    So far I’ve only used this technique in my work to retrieve one column (field). I wondered if this technique would work for a multiple-column result set. It turns out it will. Here’s one last illustration.

    dcl-ds CustInfo     qualified;
       List         char(512)             pos(1);
       Record                   dim(13)   pos(1);
          Account   zoned( 6)             overlay (Record: 1);
          LastName  char ( 8)             overlay (Record: *next);
          City      char ( 6)             overlay (Record: *next);
          State     char ( 2)             overlay (Record: *next);
    end-ds CustInfo;
    
    dcl-s  CX               uns( 5);
    
    exec sql
       select listagg(digits(c.CusNum) concat
                             c.LstNam  concat
                             c.City    concat
                             c.State)
       within group (order by c.State, c.LstNam) as List
         into :CustInfo.List
         from qcustcdt as c
        where c.BalDue >= 100;
    
    CX = 1;
    dow CustInfo.Record (CX) <> *blanks;
       dsply CustInfo.Record (CX);
       dsply CustInfo.LastName (CX);
       CX += 1;
    enddo;  
    

    It turned out to be a simple matter of concatenating the values of four fields in LISTAGG and defining the four fields in the data structure.

    There you have it — a multi-row SELECT INTO into an array — and I was told that it couldn’t be done.

    RELATED STORIES

    LISTAGG

    SQL PL–The FOR Loop

    Geico Cavemen: The Apology

    It Couldn’t Be Done

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, FHG, Four Hundred Guru, IBM i, LISTAGG, QCUSTCDT, RPG, SELECT INTO, SQL

    Sponsored by
    UCG Technologies

    CYBER-ATTACKS ON THE RISE. PROTECT WITH THE TRIPLE PLAY.

    COVID-19 has not only caused a global pandemic, but has sparked a “cyber pandemic” as well.

    “Cybersecurity experts predict that in 2021, there will be a cyber-attack incident every 11 seconds. This is nearly twice what it was in 2019 (every 19 seconds), and four times the rate five years ago (every 40 seconds in 2016). It is expected that cybercrime will cost the global economy $6.1 trillion annually, making it the third-largest economy in the world, right behind those of the United States and China.”1

    Protecting an organization’s data is not a single-faceted approach, and companies need to do everything they can to both proactively prevent an attempted attack and reactively respond to a successful attack.

    UCG Technologies’ VAULT400 subscription defends IBM i and Intel systems against cyber-attacks through comprehensive protection with the Triple Play Protection – Cloud Backup, DRaaS, & Enterprise Cybersecurity Training.

    Cyber-attacks become more sophisticated every day. The dramatic rise of the remote workforce has accelerated this trend as cyber criminals aggressively target company employees with online social engineering attacks. It is crucial that employees have proper training on what NOT to click on. Cyber threats and social engineering are constantly evolving and UCG’s Enterprise Cybersecurity Training (powered by KnowBe4) is designed to educate employees on the current cutting-edge cyber-attacks and how to reduce and eliminate them.

    A company is only as strong as its weakest link and prevention is just part of the story. Organizations need to have a quick response and actionable plan to implement should their data become compromised. This is the role of cloud backup and disaster-recovery-as-a-service (DRaaS).

    Data is a company’s most valuable asset. UCG’s VAULT400 Cloud Backup provides 256-bit encrypted backups to two (2) remote locations for safe retrieval should a cyber-attack occur. This is a necessary component of any protection strategy. Whether a single click on a malicious link brings down the Windows environment or an infected SQL server feeds the IBM i, once the data is compromised, there is no going back unless you have your data readily available.

    Recovery is not a trivial task, especially when you factor in the time sensitive nature of restoring from an active attack. This leads to the third play of the Triple Play Protection – DRaaS.  Companies have myriad concerns once an attack is realized and a managed service disaster recovery allows employees to keep focus on running the business in a crisis state.

    The combination of training employees with secure backup and disaster recovery offers companies the best chance at avoiding financial disruption in an age of stronger, more frequent cyber-attacks.

    Reach out to UCG Technologies to discuss your company’s security needs and develop a data protection plan that fits you best.

    ucgtechnologies.com/triple-play

     800.211.8798 | info@ucgtechnologies.com

     

    1. https://theconversation.com/cyberattacks-are-on-the-rise-amid-work-from-home-how-to-protect-your-business-151268

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Clearlake Re-Acquires Precisely In $3.5 Billion Deal Thoroughly Modern: Running CA 2E Applications? It’s Time To Modernize The UI

    2 thoughts on “Guru: SELECT INTO And Arrays”

    • Steven Easton says:
      March 8, 2021 at 9:34 am

      Thanks Ted, I used FETCH last month, since I didn’t know this technique.

      Reply
    • Susan Gantner says:
      March 8, 2021 at 11:15 am

      Hi Ted. Wow – you must hate the lack of SELECT INTO a DS array support as much as I do! I applaud the innovation but for my own use I think I’ll stick with the cursor approach – ugly as it is, it seems a tad more understandable than this. Admittedly that’s likely a reflection of my lack of experience with things like listagg.

      An idea – RFE (Request for Enhancement) – the place for all of us to register our votes for enhancements. Despite my own strong feelings on the subject I had not looked for an RFE until your tip inspired me.

      The bad news: I found there is an RFE but IBM declined it back in Dec 2020 (not that long ago!) It only had a paltry 28 votes when I found it.

      Better news: It appears we can still register votes for the RFE (29 votes after I voted!) If more like-minded people were to add votes to this RFE, perhaps IBM would re-consider.

      If you hate this lack of select-into array support as much as Ted & I, please vote here:
      http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=141665

      If significant numbers of votes indicate strong support, I’ll take the “to do” to ask IBM to take another look. It may not work. But it only takes a minute or so to add your voice to the choir. Maybe we can make a difference.

      Reply

    Leave a Reply Cancel reply

TFH Volume: 31 Issue: 18

This Issue Sponsored By

  • ProData
  • Fresche Solutions
  • UCG Technologies
  • Precisely
  • Trinity Guard

Table of Contents

  • We Want IBM i On The Future Power E1050
  • Thoroughly Modern: Running CA 2E Applications? It’s Time To Modernize The UI
  • Guru: SELECT INTO And Arrays
  • Clearlake Re-Acquires Precisely In $3.5 Billion Deal
  • Ansible Automation Story Gets Better on IBM i

Content archive

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

Recent Posts

  • Big Blue Unveils Spring 2021 IBM i Technology Refreshes
  • Thoroughly Modern: Innovative And Realistic Approaches To IBM i Modernization
  • Guru: Web Services, DATA-INTO and DATA-GEN, Part 2
  • Back To The Future With A New IBM i Logo
  • IBM i PTF Guide, Volume 23, Number 14
  • When Cloud Meets DevOps on IBM i
  • JD Edwards Roadmap Reveals Decisions To Be Made
  • IBM Completes Migration of Knowledge Center to IBM Documentation
  • Four Hundred Monitor, April 7
  • Crazy Idea Number 615: Variable Priced Power Systems Partitions

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 © 2021 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.