Using BIF in Calculations Within DB2 Web Query for i
Built-in functions (BIFs) have been part of the IBM i ecosystem for years. This article examines the BIFs that are part of IBM DB2 Web Query for i.
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 below), 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 below). 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 below. 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 below), 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.
Now let’s examine the DEFINE window more closely (see Figure 5 below). First, notice that the corrected DATEDIF function has a closing parenthesis. At the left is an area to specify the field and its logic. At the right are the sources of data or functions. The buttons at the bottom left enable you to select numbers, relations, mathematical operators and logic. You can use these buttons to build logic or type on your keyboard; typing is likely faster once you understand the syntax. This window has multiple panes on the right that are used differently depending upon the logic needed to calculate the desired results.
The upper right contains several buttons to influence the view for the right-side pane. The default view shows the fields available in your query’s data source. Clicking the view buttons changes the way the fields are organized: between hierarchy views, detail views with formats, descriptions and sizes. To insert a field from the incoming data into the calculation canvas, double-click the data field or row in the right pane. At the far right top of this pane is another button labeled Fx. This brings up the function browser.
Like IBM, the folks at Information Builders Inc. (IBI), who work with IBM to provide technology in Web Query, are big on BIFs. Web Query supports many BIFs to accomplish common tasks―and a few obscure ones, too. These functions are part of the Web Focus language, developed by IBI, that lies beneath Web Query. At least six different categories of BIFs are available in Web Query (see Figure 6 below).
There are multiple BIFs in each of these categories, including more than 40 for date and time operations alone (see Figure 7 below; click to view larger). The function panel shows the function’s name and syntax for parameters required to make each work. To insert a function into the calculation pane, you can simply key it in or double-click the function line. The latter places the function’s “template” into the window; at this point you’ll have to replace the function’s default names with names from your report data. Highlight the template parameter name and then double-click a data row to swap your report field for the positional parameter in the function.
Perhaps you’re wondering whether you’ll know how to fill in these functions, and what’s required for each individual parameter? Those are good questions! To get you going quickly, IBM provides Web Query online help documentation, including descriptions of the various functions, usage examples and much more. A partial excerpt of parameter documentation for the aforementioned DATEDIF function can be seen in Figure 8 below. It explains the three parameters. Typical of these functions, help depicts the required parameters, data types, and usage examples.
Another date-related function, DATEMOV, enables you to determine a new date in relationship to a base date. For example, suppose you wish to find the next business day after a known date. Using DATEMOV as follows―DATEMOV( ORDERDATE , ‘NBD’)―with a USA order date value of 01/20/2017 would return 01/23/2017, indicating the next business day after Friday, January 20, 2017, is Monday, January 23, 2017. Depending upon the 3-character parameter being utilized, DATEMOV can find date positions for beginning/end of month, week or year, the next/prior business day, etc.
In the character manipulation category, you have functions to find strings in a character field, perform substring operations, find length of strings, change case or justify text at left or right of a field. Consider this example:
Function LOCASE(20, MYCHARFIELD, ‘A20’) changes a 20-character field to lower-case, while its counterpart, UPCASE, does the opposite. If MYCHARFIELD originally contains the mixed- case “P/N a3b7CV29XXX01”, a result after LOCASE would contain “p/n a3b7cv29xxx01”, while a result from UPCASE would be “P/N A3B7CV29XXX01”.
In addition, there are system functions that allow you to retrieve values for DATE, TIME and USER-ID ― any of which may be helpful in your reporting.
Finally, let’s spend a moment examining , NUMBERLOGIC, a calculated field whose’s value will be based on the numeric value in the NUMBERDAYS field. This requires several IF statements to analyze number ranges and assign the new value on the THEN clause. The various buttons available for the calculation pane allow you to formulate equations, nest various functions or write IF/THEN/ELSE logic.
In Figure 9 below, a mistake shows how Web Query reacted when I tried to shortcut the test of NUMBERDAYS. Included are the resulting error and my corrected syntax. My code, highlighted in red, triggered the error, and my corrected code (with green highlight) is all displayed. The problem occurred because I didn’t specify the NUMBERDAYS variable after the AND on my logic test. Like RPG, if you want to make a compound comparison, variable, operator and test value must be listed for each individual comparison.
Figure 9
Looking Ahead
When you can’t find the function you need among those provided by Web Query, you can always roll your own. That’s right, it’s possible to use SQL functions and user defined functions within Web Query. Why might you do this? Perhaps you have existing business logic to calculate a result value that you wish to use in Web Query. Or maybe you have an SQL function that manipulates data beyond Web Query’s capabilities. The details are beyond the scope of this article, but I’ll write about them in the future, so stay tuned.
In the meantime, I hope this overview of BIFs in Web Query is helpful and leads to improved reporting!