Web Query - IBM i user-defined function (UDF) with a DEFINE for wildcard and input parameter

How To

Summary

WebQuery allows parameters to be passed to the report but there isn't a complete option to allow wildcarding

Objective

One example is to use an IBM i user-defined function (UDF).

This TechNote provides a simple that can be modified to suit your needs.

This example is AS-IS and is meant to show the basics of this option.

Environment

This example uses the HR file in QWQCENT, shipped with WebQuery.

Steps

Determine which IBM i schema you want to create the user-defined function.

In this example we'll use QWQREPOS.

This is the library that contains the source for your reports (.fex) so it can be a logical place to store the user-defined function.

SQL source of the user-defined function.
Test the user-defined function using Access Client Solution's Run SQL Scripts before trying to use within WebQuery.

SET CURRENT SCHEMA = QWQREPOS; -- schema location this user-defined function

CREATE or replace FUNCTION WildCardIt (IN_VAL varchar(30) )
RETURNS varchar(32)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION
BEGIN
DECLARE out_val varchar(32);
SET out_val = '%' concat TRIM(in_val) concat '%';
RETURN out_val;
END ;

All WildCardIt does is take an input string (variable length of 30),

concatenate a % (wildcard) in front of that string,

concatenate a % at the end,

return as a variable length character string of 32.

Sample output:

select QWQREPOS.WildCardIt('Kevin')
from SYSIBM.SYSDUMMY1

returns

%Kevin%

Within WebQuery InfoAssist, create a DEFINE that calls user-defined function WildCardIt.

This example will use this wildcard functionality to match with QWQCENT/HR's Last Name column.

Breaking that down: SQL.QWQREPOS.WildCardIt ( "Last Name" )

- the SQL is required. This is telling WebQuery that it's an SQL function on the IBM i.

- QWQREPOS is the schema location of the user-defined function.

- WildCardIt is the SQL function

- Last Name is the column from QWQCENT/HR

Now add a filter using this DEFINE field

When you run this report you'll be prompted for SearchValue. In this example I'm searching for last names that have 'am'

Results:

SQL Procedures, Triggers and Functions on IBM DB2 for i

Document Location

Worldwide

[{"Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m50000000Cac4AAC","label":"IBM i WebQuery->Report Development"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Version(s)"}]

Historical Number

N1010784

Was this topic helpful?

Document Information

Modified date:
25 November 2020

UID

ibm16371306

Tips