Using BIF in Calculations Within DB2 Web Query for i

BIF in Calculations

Built-in functions (BIFs) have been part of the IBM i ecosystem for many years. As an IBM i developer, the term likely makes you think of functions that IBM provides for use within an RPG or CL program source. Alternatively, you may associate BIFs with the many functions in SQL. This article, however, examines the BIFs that are part of IBM DB2 Web Query for i.

The purpose of any BIF is to make life easier by encapsulating a process and enabling you to call it. BIFs are like a black box: you know they perform specific work―for instance, accepting input parameters and yielding a return value―but you don't have to concern yourself with these specifics.

A sample function for changing a field's text case from its current format to upper case might look like this: Uname = UPPER(Name). This isn't an actual Web Query function but an illustration of behavior to assign the Uname field the upper-case version of the Name field (i.e., from 'Ibm Rochester' to 'IBM ROCHESTER'). BIFs are real time-savers. They spare you the time and effort needed to write code to parse the value, determine length, examine each character, make case changes, etc. Returning to my point: we may not know what's in the black box, but we do know it will do its thing, each and every time.

Accessing BIFs

The Web Query InfoAssist Designer offers access to numerous functions that can speed your report development. Let's run through the process for using several of these BIFs. We'll also look at the capability to add logic in a created field.

To view available functions in InfoAssist, select the Data menu, which switches to the Data Ribbon menu (see figure 1), enabling you to select a type of calculation. Calculations allow you to use database fields from your data source with BIFs and return a result value. The new field you're creating acts as the return parameter. The process is done with a DEFINE at the detail record level or with a COMPUTE at the summary level.

To create a new detail field, select the Data menu and click Detail (DEFINE). A new window appears, allowing you to create a new field and specify the calculation required. This field becomes part of your report. The DEFINE window contains entry areas for the name, format (or data type) and size (if required for the type). It also includes a canvas area for any logic required to create the new field.

The syntax of a function named DATEDIF can be used to determine the span of days (or date difference) between two dates: TODAYDATE and ORDERDATE (see figure 2). The resulting value is placed in the new NUMBERDAYS field with a format of Integer and length of 8. Clicking the Format button to the left of the format field prompts you to select one of the various formats, including Integer, Date, Decimal and Alphanumeric. Notice the additional buttons below the canvas area where the calculation takes place. These buttons are designed to assist in formulating logic or calculations. I'll elaborate in a bit.

The canvas area logic can be as simple as an equate, where the new field takes on the contents of another data field, or a calculation using several fields. It can be more complex by including multiple functions or logic spanning many lines using IF/THEN/ELSE statements. You can also accomplish concatenation, using symbols not unlike those in CL programs. Once the NUMBERDAYS field is defined and accepted (click OK), you can use it as you would any other database field. The result from running a report, using the NUMBERDAYS field calculated with the DATEDIF function is shown in figure 3. Notice the two original dates and the number of days between them.

More Complex Uses of BIFs

Let's pause so I can emphasize something about BIFs in Web Query. Not only can you use them for one-off conversions from one data type or format to another, you can also create more extensive logic and use multiple BIFs, all of which will be executed to create a new result field. BIFs can be nested, as you you might do in RPG. As you may have already recognized, in a detail (DEFINE), logic applies to individual records, while summary (COMPUTE) applies to summarized totals.

If you make an error in the field definition area, you'll be notified when you click OK, and prevented from accepting the result. While I find some InfoAssist error notifications cryptic, for the most part they're spot on. Some of my beginner errors in calculations resulted from typing the function incorrectly, leaving off a parenthesis at the end of a function (as seen in figure 4), or trying to use relational operators rather than clicking the buttons for relational comparisons. Note that Web Query calculations use EQ instead of the = sign, GT vs > sign, etc.

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



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