If you spend enough time handling end user app requests, you’re bound to run into a case where someone wants to see data from a detail level file inside a grid displaying header-level data. For instance, a customer service rep might ask you to integrate a list of line items into a customer orders app that currently only shows header information.
Upon hearing such a request, you might respond, “So you want to be able to click on an order row to drill down and see all the items?”
“Oh no,” says the user, “I don’t want to click on anything. I just want to see all the product numbers on the order listed in a single cell, right alongside the customer name and ship-to address!”
(Forehead slap.) And so begins your attempt to explain the concept of normalized data and the complexity of what they’re asking for. OR, if you’re on IBM i OS 7.2 or higher, you can use a simple SQL trick to quickly give the user what they think they want and see if it actually works for them.
The key behind this trick is a somewhat obscure SQL function called LISTAGG (“list aggregate”) in your data source. Simply put, LISTAGG takes data elements you would normally show vertically in multiple rows and compresses them into a single horizontal list, typically comma-delimited. You can see the official IBM documentation here, but you’ll likely find it easier to digest via a simple example. So let’s take a stab at that user request to mix customer order detail into a customer order header list…
We’ll start with a simple SQL data source that pulls in elements from a customer order header file, DEMOORD_H:
select ORDERNO, STATUS, SHPCITY, SHPSTATE, SCHDATE
from DEMOORD_H
order by ORDERNO
The result of this statement should result in a data source preview panel that looks like this:
Now let’s do as the user wants and pull in the items for each order from the detail file, DEMOORD_D. But instead of joining to the file via the main FROM clause, we’ll insert a subquery linked by the order number that uses our magic LISTAGG function as follows (changes in bold):
select ORDERNO, STATUS, SHPCITY, SHPSTATE, SCHDATE,
(select listagg(trim(ITEM),', ') from DEMOORD_D as D
where D.ORDERNO=H.ORDERNO) as ITEMS
from DEMOORD_H as H
order by ORDERNO
As you might surmise, the two parameters on LISTAGG are asking it to take the trimmed item numbers on the order and use comma+space as a delimeter. The results of this statement results in a mix of header and detail data that looks like this (note the comma-delimited ITEMS column):
Of course, if you delivered a grid app in this format it’s almost guaranteed your users would come back to ask that the items be listed alphabetically. To do that we need to add a slightly peculiar “within group(order by xxx)” clause to the statement, as shown here:
select ORDERNO, STATUS, SHPCITY, SHPSTATE, SCHDATE,
(select listagg(trim(ITEM),', ')
within group(order by ITEM)
from DEMOORD_D as D
where D.ORDERNO=H.ORDERNO) as ITEMS
from DEMOORD_H as H
order by ORDERNO
Resulting in this:
And finally, to accommodate cases where an order contains the same item multiple times — as is the case in this example on order 10001 — we can add DISTINCT inside the LISTAGG function to only include such items once in the list:
select ORDERNO, STATUS, SHPCITY, SHPSTATE, SCHDATE,
(select listagg(distinct trim(ITEM),', ')
within group(order by ITEM)
from DEMOORD_D as D
where D.ORDERNO=H.ORDERNO) as ITEMS
from DEMOORD_H as H
order by ORDERNO
Leaving us with this result:
Of course your approach to using LISTAGG may require some special handling if the number of detail records associated with a header row could be exceedingly large (i.e., giant customer orders with hundreds of lines). For tips on handling that, and examples of other powerful ways to leverage LISTAGG, check out these posts from Simon Hutchinson at RPGPGM.com and Ted Holt at IT Jungle.
The LISTAGG function is available to IBM i sites running 7.4, or 7.3 at TR2+, or 7.2 at TR6+. You must also be running the latest Valence build (6.0.20210226.1) in order to use the “within group(order by xxx)” clause to sort the LISTAGG output.