Using SQL Functions in a DEFINE field in Db2 Web Query for i

The capabilities provided through the use of SQL and user-defined functions brings more power to the already powerful Web Query reporting system.

In a prior article, I noted that Web Query DEFINE can be used to create a new field in an InfoAssist report. To quickly recap, a field can be created at the row level, with its value set by the expression editor.

Each DEFINE uses the expression editor in some combination of ways. The new field value could be a literal, a value from another field, a calculated value (i.e., price * qty = extended_price), some IF/THEN/ELSE logic within the expression, or some of the many built-in-functions (BIFs) that exist in Web Query. The DEFINE logic is processed for every detail row of the report. (Note: COMPUTE fields operate similarly but apply to summary or aggregated totals in the report, as opposed to detail row data.)

A DEFINE can also be used to create a field that calls an SQL function to return a value when a report is executed. Why would you do this? Perhaps you require a numeric or character manipulation of your data, and no Web Query function exists to perform this task. Because Web Query writes SQL that “communicates with” Db2, the DEFINE allows you to specify the Db2 function you wish to utilize.

To demonstrate this process, let’s start with a couple examples of existing SQL functions being called by Web Query. The first, MONTHNAME, is a standard function that takes a date data type field as input and returns the name of the month (i.e., ‘2017-12-12’ = ‘December’) as a character string. The SQL syntax for this function is MONTHNAME(DATE). To make this work in Web Query, you define an alphanumeric field with a length sufficient to return the largest expected month name value. The expression uses the SQL.XXXXXXXX(PARMS) syntax, where XXXXXXXX represents a function name and any required parameter is in parenthesis. In this case, a date field must be passed from the input datasource (see Figure 1 below).

This is simple enough, but you still need to be careful. If the field being passed isn’t in the right format for the SQL function being attempted, you’re liable to see errors or some other unwanted outcome. I know this from experience. Here’s what’s happened as a result of errors I’ve made:

1. While defining the field, I received an error message from the expression editor.
2. I’ve received execution time errors when running the report. In one instance, I passed a numeric field containing a date (rather than a date data type) to MONTHNAME, prompting this message: Argument 1 of function MONTHNAME not valid.
3. I received a blank result from the function when I passed a character field instead of a date to MONTHNAME. The blank result appeared in the report column.

Other SQL Functions

Web Query allows you to perform many other SQL functions that come with Db2. For starters, you can change fields to all caps or all lower case using the Db2 functions UPPER(textfield) and LOWER(textfield). Given an initial character value of ‘January’, these return ‘JANUARY’ and ‘january’, respectively.

In Figures 2 and 3 below, I’ve created new fields that would perform the upper or lower function on an existing field, Define_1, which contains the month name I previously retrieved. These examples demonstrate that the SQL function name syntax is not case-dependent in the DEFINE window; either SQL.UPPER or SQL.upper works equally well:

I should clarify something for readers familiar with both SQL and Web Query functions. Web Query has BIFs called UPCASE and LOCASE that provide the same capabilities as UPPER and LOWER. The difference is that the Web Query functions take three parameters related to length, input field and output length/type, which is typical Web Query style for its BIF parameters. Now, this doesn’t imply that the SQL functions perform better than the corresponding Web Query BIFs. I haven’t benchmarked this. I only mention these common BIFs to illustrate the techniques found in Web Query. If you wish to experiment with case manipulation, Web Query includes several functions for doing mixed-case.

Another way to manipulate data when the needed function isn’t present in Web Query is by using user defined functions you’ve developed. Suppose my report needs to convert temperatures from Fahrenheit to Celsius. Unfortunately, temperature conversion isn’t among the available numeric and format conversion BIFs (the list is presented in Figure 4 below).

To fulfill this report requirement, I can create a simple SQL function that converts temperatures from the Fahrenheit to Celsius scale. This function would take an integer temperature in Fahrenheit as input, and return its Celsius counterpart, also as an integer value (see Figure 5 below).

Once the function is known to Db2, it can referenced in the same fashion as other BIFs supplied within Web Query. The definition of the resulting Temperature_C field, using the TempF field as input to the SQL function CELSIUS, is seen in Figure 6 below. The SQL function must exist in a library that’s accessible during Web Query InfoAssist execution.

After adding the original Fahrenheit temperature and the calculated Celsius result fields to my report, I can see that the records returned show data in both formats, courtesy of the SQL conversion function (see Figure 7 below).

Let’s look at one more Web Query function that allows you to reference SQL functions within a report. The DB_EXPR() function uses a different format so you can include SQL functions in a DEFINE expression, using fields from your data source. As you can see, it requires any synonym field(s) to be enclosed in quotation marks. In this example, we stack the SQL function (CHAR) and the user-defined function (CELSIUS) to convert the returned integer Celsius value to character before concatenating it with some text, returning a character string for the report. The resulting field simply states the temperature that was converted. The DB_EXPR() format is shown in Figure 8 below and the resulting report data is displayed in Figure 9 (also below).

A Powerful Combination

The capabilities provided through the use of SQL and user-defined functions brings more power to the already powerful Web Query reporting system. This type of reporting can be especially valuable to sites that have valuable business logic already developed in SQL or another ILE language. We’ve shown that with an SQL function wrapper, it’s easy to expose logic and return values to Web Query. It’s just one more way that the combination of Db2 for i and Db2 Web Query for i can make your reporting tasks easier.

---