• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: Combine Related Rows Using SQL

    February 12, 2018 Ted Holt

    A reader writes: “Hey, Ted. In our ERP system, certain business objects, such as sales orders and purchase orders, can have multiple comment records. Is it possible, using SQL, to combine all the comment records for an order into one long comment and retrieve it as a column in a result set?”

    I can relate to this. I can remember supporting an ERP system where not only the orders, but the order detail lines, could have such comments. End users depend heavily on such unstructured data to do their jobs. To answer your question, yes, it is possible and it isn’t difficult.

    Let’s begin with two sales order tables — headers and comments:

    create table slsordhd
      (OrderID  dec(5), OrderDate dec(7), CustID dec(7),
      primary key (OrderID));
    
    insert into slsordhd values
    (1, 1180130, 26424),
    (2, 1180131, 75179),
    (3, 1180201, 38493);  
    
    create table slsordcmt
      (OrderID dec(5), Sequence dec(3), comment char(20),
      primary key (OrderID, Sequence));
      
    insert into slsordcmt values
    (1, 1, 'Don''t ship without c'),
    (1, 2, 'hecking with Butch a'),
    (1, 3, 't x255.'), 
    (2, 1, 'Partial shipment is '),
    (2, 2, 'NOT acceptable.'),
    (3, 1, 'Low priority');
    

    Now we have three orders with comments. In the systems I’ve worked with, the comment fields are long, at least 80 bytes, but I made them only 20 bytes to keep the amount of data manageable.

    The function that combines the rows is LISTAGG. It combines a set of string values and it can separate them with a separator string of your choice. One common use I’ve seen for this function is to build a string of comma-separated values.

    Here’s the LISTAGG function to combine the comments for each order.

    select OrderID, 
           listagg(trim(comment))
              within group(order by OrderID, Sequence) as Cmt
      from slsordcmt
     group by OrderID
    
    OrderID Cmt
    1 Don’t ship without checking with Butch at x255.
    2 Partial shipment is NOT acceptable.
    3 Low priority

    The argument to LISTAGG is the comment field without leading or trailing blanks. Only you can decide if you should retain blanks or not.

    The last line tells the query to group rows on a common order ID. GROUP BY has been around for decades, so chances are you’re very familiar with it.

    The WITHIN GROUP may be new to you. This clause is part of LISTAGG, and its purpose is to tell the query engine how to sequence the rows within each group. In this case, I said that I want the comment rows sorted by order ID and sequence number.

    The order comment is probably not useful by itself. You probably want to combine it with other data. In the following query, I use a lateral subquery to combine it with fields from the order header file. LATERAL lets me run the second SELECT, which has the LISTAGG function, for each row of the first SELECT. The good thing about LATERAL is that the second SELECT is permitted to reference columns from the first SELECT. In this case, that’s h.OrderID. If you’re still not comfortable with the lateral subquery, sometimes called lateral correlation or lateral join, see the related stories at the end of this article.

    select h.OrderID, h.OrderDate, h.CustID, x.Cmt
      from slsordhd as h, 
     lateral (select OrderID, 
                     listagg(trim(comment))
                        within group(order by OrderID, Sequence) as Cmt
                from slsordcmt as c
               where c.OrderID = h.OrderID
               group by OrderID) as x
    where h.OrderDate <= 1180131
    
    OrderID Date Customer Cmt
    1 1180130 26424 Don’t ship without checking with Butch at x255.
    2 1180131 75179 Partial shipment is NOT acceptable.

    Of course, you may want even more tables and/or views in the join. Here’s the same query with the customer master table added in order to get the customer name. I was able to join the tables first and then tack the LATERAL on afterward.

    select h.OrderID, h.OrderDate, h.CustID, cu.CustName, x.Cmt
      from slsordhd as h
      left join cust as cu
        on h.CustID = cu.CustID, 
     lateral (select OrderID, 
                     listagg(trim(comment))
                        within group(order by OrderID, Sequence) as Cmt
                from slsordcmt as c
               where c.OrderID = h.OrderID
               group by OrderID) as x          
    where h.OrderDate <= 1180131
    
    OrderID Date CustID Customer Cmt
    1 1180130 26424 ACME Don’t ship without checking with Butch at x255.
    2 1180131 75179 SUPERIOR Partial shipment is NOT acceptable.

    That’s all there is to it. Play with it a bit and you’ll be a LISTAGG wizard in no time.

    RELATED STORIES

    More V5R3 SQL Enhancements

    Using Lateral Correlation To Define Expressions In DB2 For i

    LISTAGG

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

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

    Sponsored by
    Manta Technologies

    The Leader in IBM i Education!
    Need training on anything i?
    Manta is all you need.

    During the month of May, when you purchase a license for the Free-Form RPG Programming series for any term (one, two, or three years) and any user license level, and you will get the equivalent license for the Coding Free-Form RPG course for FREE.

    130 courses and competency exams on:
    · IBM i operations
    · System Management and Security
    · IBM i Programming Tools
    · Programming in RPG, COBOL, CL, Java
    · Web Development
    · SQL, DB2, Query

    Product features:
    · Runs in every popular browser
    · Available 24/7/365
    · Free Student Reference Guides
    · Free Student Administration
    · Concurrent User License
    · Built-In IBM i Simulator

    You can download our 200-page catalog and take sample sessions at MantaTech.com.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    As I See It: Bot Versus Bot NGS And LightEdge Provide New Entry To The Cloud

    3 thoughts on “Guru: Combine Related Rows Using SQL”

    • glenngundy says:
      February 12, 2018 at 9:58 am

      This is fantastic. Thank you Ted!!

      Reply
    • EWART DE SOUZA says:
      February 12, 2018 at 9:25 pm

      Hi Ted,

      The following statement does not work on my V7R1 system.

      select OrderID,
      listagg(trim(comment))
      within group(order by OrderID, Sequence) as Cmt
      from slsordcmt
      group by OrderID

      It says “Keyword GROUP not expected. Valid tokens: , FROM INTO.”

      Any idea why ?

      Thanks & Regards
      Ewart

      Reply
      • Ranjith K says:
        February 18, 2018 at 1:26 am

        SQL BiF LISTAGG added with v7. 3 R2 and v7. 2 R6…

        Reply

    Leave a Reply Cancel reply

TFH Volume: 28 Issue: 11

This Issue Sponsored By

  • Maxava
  • Profound Logic Software
  • Harkins & Associates
  • Manta Technologies
  • UCG TECHNOLOGIES

Table of Contents

  • IBM Preps Power9 “ZZ” Systems For Imminent Launch
  • NGS And LightEdge Provide New Entry To The Cloud
  • Guru: Combine Related Rows Using SQL
  • As I See It: Bot Versus Bot
  • Modernizing The IBM i Estate For Digital Transformation

Content archive

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

Recent Posts

  • POWERUp Brings IBM i Base Back Together in the Big Easy
  • New Nav for i Brings New Stuff to You
  • Why Infor’s IDF Is Important for Customer Innovation
  • Four Hundred Monitor, May 25
  • IBM i PTF Guide, Volume 24, Number 21
  • How Committed Is Big Blue To The IBM Cloud?
  • Immutable Copies Are Only As Good As Your Validation
  • Guru: IBM i *USRPRF Security
  • ERP Transitions Loom for SAP on IBM i Customers
  • Inflation Pumps Up Global IT Spending, Supply Chain Deflates It

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.