You are currently on IBM Systems Media’s archival website. Click here to view our new website.


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

SQL in Db2 Web Query

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

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.

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.



2019 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