I can't count the number of times someone has asked me if it was possible to pass parameters to a Query/400 query. In fact, the question came up several times in responses I received to this mini-series on query techniques. I suppose the answer bears repeating.
For this tip and the one that follows, we'll use the same file we created in the last issue. Here's the DDS again:
* =================================================================
* = Physical file... Employees =
* = Description..... Sample employee master file =
* =================================================================
A UNIQUE
A R EMPLOYEESR
A EMPLOYEEID 5
A LASTNAME 20
A FIRSTNAME 15
A LOCATION 10
A DEPARTMENT 10
Now consider that you have a query with record selection based on Location and Department. Here's a look at Query/400's Select Records panel.
Select Records
Type comparisons, press Enter. Specify OR to start each new group.
Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...
AND/OR Field Test Value (Field, Number, 'Characters', or ...)
LOCATION EQ 'HOUSTON'
AND DEPARTMENT EQ 'SALES'
Users often need to use different selection criteria and simply edit the query definition to modify the selection criteria before running the query. Here's one way to do just that.
You somehow need to get the parameter values into the query. Well, since Query/400 reads files, why not stuff the parameter values in a file and figure out how to get Query/400 to read them?
Because your query needs a parametric value for Location and for Department, you need to create a file with those fields. Because you're going to define a Query/400 that joins the Employees file with this file containing parameter values, you also need to determine join information and specify it in the parameter file. It will be clearer as to how I determined the join information later, so I'll defer that discussion until then. For now, simply note that the file has field DummyID, which I use in the join.
* =================================================================
* = Physical file... EmpParms =
* = Description..... Employee query parameters file =
* =================================================================
A R EMPPARMSR
A DUMMYID 5
A LOCATION 10
A DEPARTMENT 10
Now, let's look at how you can create a Query/400 query that will use the values in this parameter file.
Let's begin with a look at the Specify File Selections panel. Notice that the query not only specifies the Employees file, but also the EmpParms file. This tells Query/400 that we are going to join the two files.
Specify File Selections
Type choices, press Enter. Press F9 to specify an additional
file selection.
File . . . . . . . . . EMPLOYEES Name, F4 for list
Library . . . . . . YOURLIB Name, *LIBL, F4 for list
Member . . . . . . . . *FIRST Name, *FIRST, F4 for list
Format . . . . . . . . EMPLOYEESR Name, *FIRST, F4 for list
File ID . . . . . . . T01 A-Z99, *ID
File . . . . . . . . . EMPPARMS Name, F4 for list
Library . . . . . . YOURLIB Name, *LIBL, F4 for list
Member . . . . . . . . *FIRST Name, *FIRST, F4 for list
Format . . . . . . . . EMPPARMSR Name, *FIRST, F4 for list
File ID . . . . . . . T02 A-Z99, *ID
Notice that File IDs T01 and T02 are assigned to files Employees and EmpParms, respectively. We'll use this information later.
Next, we need to specify information that Query/400 will use in joining the two files. In the Specify Type of Join panel, select option 1 to join based on matched records as shown below.
Specify Type of Join
Type choice, press Enter.
Type of join . . . . . . . . 1 1=Matched records
2=Matched records with primary file
3=Unmatched records with primary file
You'll then need to enter join criteria on the Specify How to Join Files panel as shown below.
Specify How to Join Files
Type comparisons to show how file selections are related, press Enter.
Tests: EQ, NE, LE, GE, LT, GT
Field Test Field T01.EMPLOYEEID NE T02.DUMMYID
Notice that I've joined the two files by the ID field; however, I've specified that records should be joined when the IDs don't match (Test = NE)! This might be counterintuitive, but it is an important element of the query technique at hand. Remember, we have a single record with parameter values for Location and Department. We must join this single record to every record in the Employees file. Knowing that the Employees file will never have a record with a blank value for field EmployeeID, I set the DummyID field to blank and instructed Query/400 to join records when the IDs don't match. Because they'll never match, the test for not equal will always be true and Query/400 will join each record in file Employees with the record in EmpParms. In so doing, the values in the fields from file EmpParms are now defined and available to the query to use at will.
Now, let's see how to select records based on the values in file EmpParms. The Select Records panel below shows how you enter the criteria.
Select Records
Type comparisons, press Enter. Specify OR to start each new group.
Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...
AND/OR Field Test Value (Field, Number, 'Characters', or ...)
T01.LOCATION EQ T02.LOCATION
AND T01.DEPARTMENT EQ T02.DEPARTMENT
Because the Location and Department field names are the same in files Employees and EmpParms, I specify the File ID assigned to each file (T01 for Employees and T02 for EmpParms) to qualify the field names. Therefore, the query selects a record when the Location and Department field values in file Employees match those in file EmpParms.
Try it out. Use your favorite file editor and add a single record to file EmpParms with values for Location and Department. You should limit the number of records in the file to one with this query technique. Joining every record in the primary file to multiple records in a secondary file can have a negative effect on performance. If you need to pass multiple values for a particular field, define multiple fields in the single record.