Note: The code accompanying this article is available for download here.

My previous article, A First Look at SQL Descriptors, looked at how SQL descriptors can be used in constructing and processing dynamic SQL statements. This article examines how SQL descriptors can be used in processing the information returned through a dynamic SQL statement.

As an example, we will write a program that dynamically constructs the select clause in an SQL select statement. Traditionally, this would cause us a problem in our RPG programs since we would not be able to code the INTO clause correctly. We have

An end user has asked me to provide him a spreadsheet with two independent lists, one beside the other. Can I use SQL to satisfy his request?

–Dennis

Yes, you can. Dennis works for a manufacturer, and his data had to do with routing operations. I’m going to use a more common type of data to illustrate.

Let’s say we work for a company that sells dohickeys, thingamajigs, and whatchamacallits. We offer these items in various colors, but we don’t offer all items in all colors.

In my last article, I promised to tell you about four new OLAP aggregate functions: FIRST_VALUE, LAST_VALUE, nTH_VALUE and RATIO_TO_REPORT. These valuable functions can reference data from other rows in a query result set relative to the current row. This tip covers the fourth function in the list, RATIO_TO_REPORT.

The RATIO_TO_REPORT Function

The new RATIO_TO_REPORT function is heaven sent, as it makes it easy for developers to avoid writing an UGLY arithmetic expression to do a simple calculation common in business. RATIO_TO_REPORT simply divides a numeric column or expression value in the current row by a SUM aggregate of