Simplify your SQL development by staying current

Since I returned to the IBM i world last Fall, I’ve spent time reviewing all of the Db2 for i and SQL enhancements that have been delivered during the 5+ years that I was off working on Watson.  The Rochester development team has definitely been busy cranking out some great functionality while I was away. It’s been nice to see improvements to the SQL standards that deliver real efficiency for developers.

One of those SQL standard additions that caught my eye is the LISTAGG aggregate function.  The documentation states that this function aggregates a set of string elements into one string by concatenating the strings.  I think a simpler real-world benefit description is that it makes it easy to combine values from multiple rows into a single row. For example, you’re asked to produce a report of the shipping companies that your business uses by region and the report format needs to be the following:

NORTH	FEDEX, GARZA SHIPPING, SPEEDEE DELIVERY
SOUTH	FEDEX, TYLER TRUCKING, UPS, USPS

 

This is easily done with the LISTAGG function with this simple, but powerful SQL statement:

SELECT region, 

       LISTAGG(DISTINCT carrier, ', ') WITHIN GROUP(ORDER BY carrier)
   FROM shipping_method GROUP BY region

I highlight the simplicity of this solution because you could do this in SQL before with recursive SQL syntax, but those types of SQL statements are longer and difficult to understand. In this article, I provide more details on LISTAGG and contrast it with the recursive SQL solutions.

I believe a feature like this is also a good reminder of the benefits of staying current on your IBM i release level and Database Group PTFs.  The more current your systems are, the more Db2 & SQL features there are available to simplify life for your developers.