• 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
    Racksquared

    It’s time for IBM Power in the Cloud!

    Stop buying hardware and make the move to the cloud. It’s easier and more cost effective than you might think.

    • IBM Power in the Cloud
    • IBM Power Backup Solutions
    • IBM Power High Availability and DR solutions
    • IBM Power Colocation with Management and Monitoring

    Let’s talk about your business needs.

    Call: 855-380-7225

    Email: Sales@racksquared.com

    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

  • N2i Gains Traction Among IBM i Newbies
  • Realizing The Promise Of Cross Platform Development With VS Code
  • 2023 IBM i Predictions, Part 3
  • Four Hundred Monitor, January 25
  • Join The 2023 IBM i Marketplace Survey Webinar Tomorrow
  • It Is Time To Have A Group Chat About AI
  • 2023 IBM i Predictions, Part 2
  • Multiple Vulnerabilities Pop Up In Navigator For i
  • Participate In The 2023 IBM i Marketplace Survey Discussion
  • IBM i PTF Guide, Volume 25, Number 4

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.