• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: Scalar Use of LISTAGG

    August 24, 2020 Ted Holt

    The SQL LISTAGG function is as handy as a pocket. Only recently I used it to build a string of comma-separated values (CSV) to populate a drop-down box. It sure beats a cursor and a loop. I’ve noticed in my reading that LISTAGG can be used for both aggregate and scalar purposes under Oracle. Db2 doesn’t support the scalar use, but I found another way to do the same thing.

    To see what I’m talking about, check out example 7 in the article Oracle LISTAGG Function Usage, Tips & Examples, by Ben Brumm. I recommend studying the entire article; there’s a lot of good information there.

    The IBM Knowledge Center is very clear about the role of LISTAGG in Db2 for i. There’s only one rule on the 7.4 page that describes LISTAGG, and that rule says “LISTAGG cannot be used as part of an OLAP specification.” But that doesn’t mean we can’t do the same sort of thing in a different way.

    To illustrate, let’s set up a database of employee information.

    declare global temporary table depts
      (ID dec(3), Name char(30));
    
    insert into session.depts values
    (   1, 'Accounting'),
    (   2, 'Production'),
    (   3, 'Research & Development'),
    (   4, 'Quality Assurance'),
    (   5, 'Customer Service'),
    (   6, 'Purchasing'),
    (   7, 'Information Technology');
    
    declare global temporary table emps
      (ID dec(3), Last char(12), First char(12), DeptID dec(3));
      
    insert into session.emps values
    (  1, 'Dover',   'Ben',    2),
    (  2, 'Butler',  'Peanut', 7),
    (  3, 'Makit',   'Willie', 2),
    (  4, 'Wont',    'Betty',  2), 
    (  5, 'Kake',    'Patty',  7),
    (  8, 'De Lion', 'Dan',    1);
    

    Now let’s play with LISTAGG. Here’s a simple query to build a CSV string of all the department names.

    select listagg(trim(d.Name),',')
              within group(order by d.Name) as list
      from session.depts as d
    
    LIST
    ================================================================
    Accounting,Customer Service,Information Technology,Production,Purchasing,Quality Assurance,Research & Development
    

    If that wraps in your browser, please forgive me.

    There is no GROUP BY clause, so the entire result set is treated as one group. This is the sort of query I used to load the drop-down box.

    Let’s try another one.

    select d.Name, 
           listagg(trim(e.Last),',')
               within group(order by e.Last) as Workers
      from session.emps as e
      join session.depts as d
        on e.DeptID = d.ID
    group by d.Name, d.ID
    order by d.Name
    
    Name                      Workers
    =======================   ================
    Accounting                De Lion
    Information Technology    Butler,Kake
    Production                Dover,Makit,Wont
    

    In this example, there is a GROUP BY. LISTAGG retrieves the employees for each department. There are two ORDER BY clauses in this query. The first one sorts the employees of a department by last name. The second one sorts the result set.

    Both queries are summary queries. The result set cannot have non-grouping columns (fields). If we want non-grouping columns, we must use a detail query with LISTAGG. Since LISTAGG is not a scalar function, we can embed it in a column, like this:

    select e.First, e.Last, e.DeptID,
           (select listagg(trim(x.Last),',') within group(order by x.Last) 
              from session.emps as x 
             where x.DeptID  =  e.DeptID) as Workers
      from session.emps as e
     order by e.Last
    
    First      Last      DeptID   Workers
    ========   ========  ======   ================
    Peanut     Butler         7   Butler,Kake
    Dan        De Lion        1   De Lion
    Ben        Dover          2   Dover,Makit,Wont
    Patty      Kake           7   Butler,Kake
    Willie     Makit          2   Dover,Makit,Wont
    Betty      Wont           2   Dover,Makit,Wont
    

    The SELECT statement in the fourth column selects all the workers for the department on that row of the result set. EMPS is queried a second time for the department on that row. The result is a detail report with an aggregate in each row.

    Depending on the situation, it may make more sense to omit the person on that row from the list.

    select e.First, e.Last, e.DeptID,
           (select listagg(trim(x.Last),',') within group(order by x.Last) 
              from session.emps as x 
             where x.DeptID  =  e.DeptID
               and x.ID      <> e.ID) as Coworkers
      from session.emps as e
     order by e.Last
    
    First      Last       DeptID   Coworkers
    ========   ========   ======   ==========
    Peanut     Butler          7   Kake
    Dan        De Lion         1   (null)
    Ben        Dover           2   Makit,Wont
    Patty      Kake            7   Butler
    Willie     Makit           2   Dover,Wont
    Betty      Wont            2   Dover,Makit
    

    The query lists the coworkers of the person on each row. Again, the SELECT in the fourth column refers to columns from the selected row and the inner SELECT to join the data.

    I can’t say enough good things about LISTAGG. The more I use it, the more I like it.

    RELATED STORIES

    Oracle LISTAGG Function Usage, Tips & Examples

    LISTAGG (IBM Knowledge Center)

    Guru: Combine Related Rows Using SQL

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, comma-separated values, CSV, DB2, FHG, Four Hundred Guru, IBM i, LISTAGG, OLAP

    Sponsored by
    Krengeltech

    When it comes to consuming web APIs on your IBM i, your options often boil down to one of two things:

    First, you end up having to rely on a variety of open source and non-RPG solutions. This adds developer complexity, taking away time that could have been better spent invested in other projects. Of course, open source software is free, but generally comes at the cost of no professional support, which adds an element of risk in your production environment. RXS is completely professionally supported, and is complemented by a staff of trained IBM i developers who can address your nuanced development challenges, head on.

    Second, if you choose not to pursue an open-source solution, you’re often left having to shake up your current program architecture with proprietary software, external dependencies, and partial RPG implementations – many of which are sub-par compared to RPG-XML Suite’s wide range of features. RXS aims to simplify the efforts of developers with tools like code generators, useful commands, and subprocedures written in 100% RPG – no Java. Because they are entirely RPG, the RXS subprocedures are easy to add to new or existing ILE programs and architecture, helping to cut your development time. RPG-XML Suite offers powerful capabilities in an accessible, easy-to-implement format.

    With RPG-XML Suite, you can accomplish a variety of complex tasks, such as:

    • Calling REST and SOAP web services from your IBM i
    • Offering APIs from your IBM i
    • Creating JSON & XML
    • Parsing JSON & XML
    • Text manipulation, Base64 encoding/decoding, CCSID handling, hashing and encryption functions, and more.

    To try RXS for yourself, we recommend a free proof of concept, which not only gives you access to all of RPG-XML Suite’s subprocedures and utilities but also includes a tailor-made software demonstration that can be used as a starting point for your future API implementations.

    For a free proof of concept, contact us at sales@krengeltech.com, or visit our website for more information.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Four Hundred Monitor, August 24 Is It Time To Rename RPG?

    Leave a Reply Cancel reply

TFH Volume: 30 Issue: 51

This Issue Sponsored By

  • Maxava
  • WorksRight Software
  • Profound Logic Software
  • ARCAD Software
  • Raz-Lee Security

Table of Contents

  • Drilling Down Into The Power10 Chip Architecture
  • Is It Time To Rename RPG?
  • Guru: Scalar Use of LISTAGG
  • Four Hundred Monitor, August 24
  • IBM i PTF Guide, Volume 22, Number 34

Content archive

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

Recent Posts

  • IBM i 7.3 TR12: The Non-TR Tech Refresh
  • IBM i Integration Elevates Operational Query and Analytics
  • Simplified IBM i Stack Bundling Ahead Of Subscription Pricing
  • More Price Hikes From IBM, Now For High End Storage
  • Big Blue Readies Power10 And IBM i 7.5 Training for Partners
  • IBM Delivers More Out-of-the-Box Security with IBM i 7.5
  • Groundhog Day For Malware
  • IBM i Community Reacts to IBM i 7.5
  • Four Hundred Monitor, May 11
  • IBM i PTF Guide, Volume 24, Number 19

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.