Documentation >
MAC-PAC Reference Library >
Reference >
Common Features >
Key Concepts and Procedures >
Creating a BPM Extract Program or Query >
Creating a Query
Creating a Query
Extract queries can output extracted information to either a file or a report. If the extracted information is output to a file, the information will be routed to the appropriate BPM mailboxes online by the BPM Asynchronous Monitor (BPMASYCMON). If the information is output to a report, the extracted information will be printed, but not routed to a user online.
Use Query/400 to create a query by completing the following screens: Work With Queries, Specify File Specifications, Define Result Fields, Select and Sequence Fields, Select Records, Select Output Type and Output Form, Define Database File Output. The following steps describe the information that should be entered on each of these Query/400 screens.
A. Work With Queries. Enter the name of the query you are creating and the library in which it should be stored.
B. Specify File Specifications. Enter the name of the extract input file and library. This is the input file from which the event information is to be extracted. If more than one input file is required, use the Add File key (F9) to add the additional files.
When extracting information from a large file, you may wish to use a logical view of the input file which only selects a subset of the records from the physical file (for example, only manufacturing orders with a status of open or firmed). Doing so will limit the number of records which need to be read.
C. Define Result Fields. Enter the names of the Extracted Data Workfile (CF201AP1) fields to which the query results should be output. In each of the corresponding Expression fields, specify the input file field name that identifies the information to be extracted.
Note: Skip this step if you are creating a query that outputs to a report. It is not necessary to define the result fields if the output will be printed rather than written to a file.
Any fields that must be formatted with information on the Extracted Data Workfile should be defined in the query with the same field names, lengths, and types as they are on the Extracted Data Workfile. For alphanumeric fields, this may require that you concatenate blanks onto the end of the field. For example, the Route Based On Data field (D0WDTM) on the Extracted Data Workfile is 15 characters long. If you are extracting the buyer code, which is 3 characters long, into this field, you will need to concatenate a field of 12 blanks onto the end of the Route Based On Data field.
Define Result Fields
Type definitions using field names or constants and operators, press Enter.
Operators: +, -, *, /, SUBSTR, ||, DATE...
Field Expression Column Heading Len Dec
RLNO digits(shrlno)
D0WCRT shono||' '||rlno||' '||shotp||' '
||shost||' '||shhldc||' '||shcstn
||' '||shcorp||' '
More...
Field Text Len Dec
SHCO Selling Company 3
SHWHS Selling Warehouse 3
SHLOC Location 3
SHONO Order Number 6
More...
F3=Exit F5=Report F9=Insert F11=Display names only
F12=Cancel F13=Layout F20=Reorganize F24=More keys
|
The Query/400 Define Result Fields Screen.
The Define Result Fields screen is used to specify the fields to which event information should
be extracted. In this sample, the “digits” function is used to convert the shrlno field to an
alphanumeric string. The 40-character event detail field (D0WCRT) is being formatted with a
concatenation of seven fields separated by spaces.
Note: It is not necessary to define result fields for those fields that are to be formatted with blanks or zeros. The maximum number of result fields that can be defined in an AS400 query is 100.
Because all the fields on the Extracted Data Workfile are alphanumeric, except the Control Value Amount, you may need to use the Query/400 “digits” function to convert a numeric value into an alphanumeric string so it can be extracted into an alphanumeric field on the Extracted Data Workfile.
You can define the 40-character Event Detail Information field (D0WCRT) by concatenating a number of different fields, with or without blanks separating them.
D. Select and Sequence Fields. Select all the fields of the Extracted Data Workfile. The sequence of the fields does not matter.
E. Select Records. Enter selection criteria for the records to be extracted. For example, you may only want to select open or released orders with a credit exception hold code and a total order amount over $10000. You may choose to do this in addition to or instead of using the control value minimum and maximum values from the event routing information if you are extracting data from a very large file and want to limit the number of records extracted.
If you wish to use a date as selection criteria (for example, all sales orders with a promise ship date equal to today), you can use the YEAR, MONTH, and DAY functions to obtain the current system date and then add these values together to get the current system date in YYMMDD format.
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 ...)
SHOST LIST 'O' 'R'
AND SHOTP LIST 'C' 'G' 'H'
AND SHTOAF GE 500000
AND SHHLDC LIST 'C' 'D' 'E'
Bottom
Field Text Len Dec
D0WDTM shco||shloc||shcrmn||' ' 15
D0WFP1 shono||' ' 15
D0WCO shcco 3
D0WWHN shwhs 3
D0WFP2 shono||' ' 15
More...
F3=Exit F5=Report F9=Insert F11=Display names only
F12=Cancel F13=Layout F20=Reorganize F24=More keys
|
The Query/400 Select Records Screen
The Select Records screen allows you to specify selection criteria for the records to be
extracted. In this sample, only records with an order status of open or released
(SHOST = O or R), an order type of regular sales order, blanket order release, or
backorder release (SHOTP = C, G, or H), a sales order total amount greater than or
equal to 500,000, and an hold code of past due accounts receivable, over credit limit, or
over order limit (SHHLDC = C, D, or E).
F. Select Output Type and Output Form. Indicate whether you want the extracted information to be output to a file or a printer.
If you choose to output to a file, enter CF201TMP as the file name for the output and specify that the existing data in the file should be replaced by entering 2 (replace file) in the Data in File field.
For more information about creating queries, refer to the Query/400 User’s Guide published by IBM®. You can also request a free sample BPM query from the MAC-PAC OPEN Support Help Desk.
Testing Your Extract Query
Once you have created your query, you should test it to make certain it extracts the proper information. If the query outputs to a report, run the query and check the output to verify the formatting of the fields and that the appropriate records were selected.
Follow these steps to test extract queries that output to a file:
A. Create a copy of CF201TMP and CF201AP1 in your own library. Change the outfile library in the query definition to your own library and run the query. For the test, the query should output to CF201TMP in your library.
B. Copy the CF201TMP file to CF201AP1 using the *ADD, *MAP, and *DROP options.
C. Check CF201AP1 to verify the formatting of the fields and that the appropriate records were selected.