IBM i > ADMINISTRATOR > DB2

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

SQL in Db2 Web Query

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, 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).

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 (Figure 5).

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. 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 (figure 7).

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 and the resulting report data is displayed in figure 9.

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.

Rick Flagler is an information technology consultant, teacher and mentor.



Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.


comments powered by Disqus

Advertisement

Advertisement

2017 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Untangling Web Query

How metadata can reduce query and report complexity

Protection From Slow Queries

Introducing DB2 for i Adaptive Query Processing

Intelligent Queries

DB2 for i Learning Statistics Engine works smarter

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters