The QRYF and companion BLDQRYSLT command simplify the use of OPNQRYF.
QRYF This is a front end to the OPNQRYF command which
supports a few parameters from OPNQRYF. It is
designed to perform simple select/sort functions. A
different interface is used for the QRYSLT
parameter. This allows more of a specification of
function and avoids having to enter apostrophe or
concatenation operators. QRYF can be used either
interactively or in CL programs.
BLDQRYSLT This command prompts for the QRYSLT parameter
information and passes back the value of the
parameter to be used in an OPNQRYF command.
BLDQRYSLT can only be used in CL programs. Not all
of the QRYSLT functions are supported. It uses the
same interface as QRYF for selection criteria.
The following shows the prompt for the QRYF command:
*********************************************************************
* *
* QRYF Front End to OPNQRYF *
* *
* File name __________ *
* Library name *LIBL *
* Selection criteria _ *
* Factor 1 or *BLANK __________ *
* Operator *EQ *
* Factor 2 or *BLANK __________ *
* Character literal *NONE_____ *
* Translate table on F1 *NONE_____ *
* And/Or relation to next select *AND *
* + for more values _ *
* Sequencing criteria _ *
* Key field __________ *
* Sequence *ASCEND_ *
* + for more values _ *
* Alw cpy - *OPTIMIZE alws sort *YES_____ *
* Member *FIRST____ *
* *
*********************************************************************
The KEYFLD specification is the same as on OPNQRYF. A list of up to
20 key fields may be specified.
The typical command to select all records in FILEA where CODE = D
would be entered on the prompter as (the <--- symbol shows what is
keyed).
*********************************************************************
* *
* File name FILEA <--- *
* Library name *LIBL *
* Selection criteria _ *
* Factor 1 or *BLANK CODE <--- *
* Operator *EQ *
* Factor 2 or *BLANK D <--- *
* Character literal F2 <--- *
* Translate table on F1 *NONE *
* And/Or relation to next select *AND *
* + for more values _ *
* *
*********************************************************************
SELECT keyword
--------------
The SELECT keyword would appear on the command entry display as:
SELECT((CODE *EQ D F2))
The F2 entry specifies that Factor 2 should be treated as a character
literal. The command will place the double quote character
surrounding the value and the QRYSLT statement would be specified on
OPNQRYF as:
QRYSLT('CODE *EQ "D"')
If you are comparing a literal against a field in the data base, the
double quote characters need to surround the literal if the field in
the data base is character.
Note that no special entries exist to describe whether a field name
or a variable name is being specified.
Normally, you will enter the SELECT statement using the prompter to
allow you to see the text descriptions of the elements making up each
selection criteria. Up to 35 selections can be made. Each Factor
(Factor 1 or 2) is limited to a maximum of 15 bytes. To save
documentation space, the following examples show the entry being made
using command syntax.
To select all AMTOWE values greater than 1000 you would specify:
SELECT((AMTOWE *GT 1000))
Since Factor 2 will contain a decimal value, you do not describe it
as a character literal.
Only character variables may be used (Same restriction on OPNQRYF).
If you want to do selection on a decimal variable, you must first
convert it to a character variable. For example, assume you want to
select all records where the AMTOWE packed decimal field in the data
base is greater than a value that will be passed in as a parameter.
You would specify:
PGM PARM(&AMOUNT)
DCL &AMOUNT *DEC LEN(5 0)
DCL &AMOUNTA *CHAR LEN(5)
CHGVAR &AMOUNTA &AMOUNT
QRYF .... SELECT((AMTOWE *GT &AMOUNTA))
Note that you do not describe Factor 2 as a character literal. It
contains a value you want treated as decimal.
You must be consistent in the definitions of F1 and F2. They must
both be character or both be decimal values.
The simplest way of remembering what to do is to think about the type
of field you want to test. If the field is decimal (e.g. AMTOWE)
you should not specify that you have a character literal.
*BLANK is a special value that can be entered into either Factor 1 or
Factor 2 to specify that a comparison against a blank (X'40') should
be made. You must specify F1 or F2 as the character literal. For
example, to select the blank code fields, specify:
SELECT((CODE *EQ *BLANK F2))
Operators
---------
The operators of *EQ, *LT, *GT, *LE or *GE are the same as on
OPNQRYF.
The operator *CT can also be specified as on OPNQRYF to describe the
'contains' function.
A new operator *WC is used to specify a 'wild card' function. Only
an equal comparison can be made with *WC.
Wild card
---------
Only the standard values are supported for wild card (asterisk and
underscore). Only the Factor 2 value can contain a wild card. Only
an equal comparison can be made. A typical specification would be:
SELECT((NAME *WC JON* F2))
This will cause an equal selection of JONES, JONATHAN etc. The *WC
operation implies that F2 will be treated as a character literal even
if you do not specify it. This example, assumes that the data in the
data base is in upper case and the names are left justified in the
NAME field.
Translate table
---------------
The translate table is used when the data in the data base is in
upper/lower case. Normally, you want the system supplied QSYSTRNTBL
translate table which can be specified by *STD. You may also name a
different translate table. A typical use is to select all the
records where ADDR contains "BROADWAY" and the data in the data base
is in upper/lower case.
SELECT((ADDR *CT BROADWAY F2 *STD))
The translate function can only be specified against Factor 1.
Both the wild card and translate function can be used in the same
selection criteria. This is normally the case when the data you want
the wild card on is in upper/lower case such as selecting all the
records that have a name beginning with 'Sch'.
SELECT((NAME *WC SCH* F2 *STD))
Expressions
-----------
You can specify an expression for Factor 1 or Factor 2. If you enter
this interactively, you must specify parenthesis around the entry
such as:
Factor 1: (XX *CAT YY)
The parenthesis tell the command analyzer to create the value XXYY.
The initial length of the input field on the prompter will not allow
a long expression to be entered. However, you can expand the field
using the special entry of & followed by a blank in the parameter
field. You could specify an expression such as:
SELECT((NAME *EQ (XYZ *CAT &VAR1 *TCAT &VAR2) F2))
And/Or logic
------------
The ANDOR parameter defaults to *AND. It allows you to control the
linking of up to 35 selection specifications. The values of *AND and
*OR do not cause additional parenthesis in the expression so that the
interpretation is made as:
(Selection 1 *AND Selection 2)
or
(Selection 1 *OR Selection 2)
If you specify:
(Selection 1 *OR Selection 2 *AND Selection 3)
the rules are the same as in the CL language (the *AND is interpreted
first) and you would receive the following:
Selection 1
or
Selection 2 and Selection 3
You can control how the and/or should be grouped by use of the *EAND
or *EOR values. These will cause additional parenthesis. *EAND
should be read as 'end parenthesis and'. *EOR should be read as 'end
parenthesis or'.
Thus you can say:
((Selection 1 *OR Selection 2) *AND (Selection 3))
by specifying *EAND as the last entry on Selection 2. For example,
to select:
CODE = D or CODE = E
and
STATE = NY
you would specify:
SELECT((CODE *EQ D F2 *NONE *OR)(CODE *EQ E F2 *NONE *EAND) +
(STATE *EQ NY F2))
The QRYF command will generate the QRYSLT statement with the correct
placement of the parenthesis.
General rules
-------------
As with OPNQRYF, you must ensure the file being processed is
SHARE(*YES). Normally, you would specify the following sequence:
OVRDBF FILE(xxxx) SHARE(*YES)
QRYF FILE(xxxx) QRYSLT(.....) KEYFLD(....)
CALL PGM(yyyy) /* Your print or display pgm */
QRYF always begins by doing a CLOF command using the OPNID of the
file you named. If the file is not open, the escape message is
monitored for and the command continues. Thus you can use the
command multiple times without having to enter the CLOF command.
QRYF generates the OPNQRYF command and then executes it using
QCMDEXC. The command is then sent back to the command entry display
as a request message and received. This allows you to duplicate and
prompt for the OPNQRYF command (if it is less than 512 bytes) to
change or add additional parameters. Thus you can use the QRYF
command to gain a 'leg up' on creating a more complex OPNQRYF
command.
The following shows how to write a CL program where the end user is
prompted for the QRYF SELECT and KEYFLD statements for FILEA. Then
the user is prompted for the fields to be printed from FILEA and the
left to right sequence using the PRTDBF command (part of the
CRTPRTPGM TAA Tool). This assumes that you have created a program
(ZFILEA) to print FILEA with the CRTPRTPGM command.
PGM
OVRDBF FILEA SHARE(*YES)
QRYF FILE(FILEA) ??SELECT() ??KEYFLD()
PRTDBF PGM(ZILEA) FIELDS(*YES)
CLOF OPNID(FILEA)
To make a reasonable end user interface, you must code for the
command failures and diagnostics to be displayed to the user. If the
end user is operating from a command entry display, you could use the
standard error handling functions as shown in the CPP for the QRYF
command. If the end user is operating from one of your own displays
(e.g. a menu), you could use the message subfile capability.
Typical BLDQRYSLT command usage
-------------------------------
The BLDQRYSLT command can only be used in CL programs to help build
the QRYSLT parameter. It uses the same user interface for specifying
the selection criteria as QRYF. Normally, you would code as follows
and prompt for the BLDQRYSLT command:
DCL &QRYSLT *CHAR LEN(2000)
.
.
BLDQRYSLT QRYSLT(&QRYSLT) SELECT(....)
OPNQRYF .... QRYSLT(&QRYSLT)
For example, assume you want to select the records from FILEA where
STATE = NY and sequence on NAME. You would prompt for the BLDQRYSLT
command and specify the selection criteria. The following describes
how it would appear in command syntax after prompting.
DCL &QRYSLT *CHAR LEN(2000)
.
.
OVRDBF FILE(FILEA) SHARE(*YES)
BLDQRYSLT QRYSLT(&QRYSLT) SELECT((STATE *EQ NY F2))
OPNQRYF FILE(FILEA) QRYSLT(&QRYSLT) KEYFLD(NAME)
CALL PGM(xxxx) /* Your print or display pgm */
CLOF OPNID(FILEA)
There is a definite ease of coding advantage for BLDQRYSLT when a
variable is required. When a variable is used in OPNQRYF, you must
specify concatenation functions to include the variable and the
apostrophe symbols (assuming a character variable is used). If the
NY value is to be passed in as a variable named &STATE and you used
OPNQRYF directly, you would have specified:
QRYSLT('STATE *EQ "' *CAT &STATE *TCAT '"')
The same function would be achieved with BLDQRYSLT as:
BLDQRYSLT QRYSLT(&QRYSLT) SELECT((STATE *EQ &STATE F2))
Because the selection statement is built in a separate command, there
is no need to describe to the command analyzer where a variable must
be changed to an actual value. With BLDQRYSLT, the &STATE variable
is changed into an actual value so that the contents of the QRYSLT
return variable are is if you had specified:
QRYSLT('STATE *EQ "NY"')
Debug Mode
----------
If you are in debug mode, OPNQRYF sends informational messages for
such things as which access path was used if any, was an access path
created, was a temporary file used, etc. These messages are resent
by QRYF.
QRYF Command *CMD
------------
The QRYF command is designed for simple select/sort functions. It
may be used interactively or in a CL program. If used interactively,
it returns the OPNQRYF command executed to allow you to gain a 'leg
up' on specifying a more complex OPNQRYF command.
The QRYF command parameters are:
FILE The qualified name of the file to be queried. The
value is passed thru to the OPNQRYF command. The
library qualifier defaults to *LIBL.
SELECT The selection criteria. Up to 35 different
selection statements can be made. Each selection
statement is made up of the following:
FACTOR 1 The field name, literal or variable to be used.
If a character literal is being used, the CHAR
LITERAL entry must contain F1. If a variable
is used, it cannot exceed a length of 15.
*BLANK is a special value which means to
compare against a blank (X'40'). It can only
be entered when the character literal is F1.
OPERATOR The operator to be used. The standard entries
of *EQ, *LT, *GT, *GE, *LE or *NE are used.
*CT may be used for the 'contains' function.
*WC may be used to specify that Factor 2
contains a wild card. Only the standard
entries of asterisk or underscore are allowed
for the wild card values. When *WC is used, an
equal comparison is assumed.
FACTOR 2 The field name, literal or variable to be used.
If a character literal is being used, the CHAR
LITERAL entry must contain F2. If a variable
is used, it cannot exceed a length of 15.
*BLANK is a special value which means to
compare against a blank (X'40'). It can only
be entered when the character literal is F2.
CHAR LITERAL An F1 or F2 must be entered if either Factor 1
or Factor 2 contains a character literal. This
tells the command which value (if any) should
be surrounded by double quotes. The default is
*NONE.
If you have a decimal field in the data base
being compared against a literal, use the
default of *NONE.
If you have a character field in the data base
being compared against a literal, use F1 or F2
depending on which factor has the literal.
TRANS TABLE The default is *NONE meaning no translate table
should be used. A translate table can only be
used on the factor 1 value. *STD can be
entered to mean use the system supplied
QSYSTRNTBL which translates lower to upper
case.
AND/OR The and/or indication describes how the next
selection should be linked. The default is
*AND meaning the next selection (if any) should
be linked in an 'and' relationship. *OR may be
specified. *EAND and *EOR may also be
specified to cause parenthesis to be placed
around the specifications. See the previous
discussion.
KEYFLD This is the field(s) to be sequenced on. A blank
value means none. This parameter is a list of up to
20 fields. *FILE may also be specified and has the
same meaning as on OPNQRYF (use the same key field
definition as the file). Ascending sequence is the
default. *DESCEND may be specified. The value is
passed thru to the OPNQRYF command.
ALWCPYDTA Allow a copy of the data. This is the same
parameter as on OPNQRYF and defaults to *YES as does
OPNQRYF. When *YES is specified, the system tries
to avoid building a temporary file, but will do so
to honor certain requests (e.g. a key field
specified on a secondary file of a join).
*OPTIMIZE should be specified when a sort (using a
temporary file) can be used. *OPTIMIZE should be
specified if you don't care whether a temporary file
is used. A sort can be a very good performing
solution because the processing program can read the
data in arrival sequence rather than in keyed
sequence. *OPTIMIZE will not cause a sort if the
number of records to be processed is small (e.g.
less than a 1000). The system determines whether it
is more efficient to build and use an access path
versus sorting and processing in arrival sequence.
*NO should be specified when you do not want the
system to create a temporary copy. If *NO is
specified, certain OPNQRYF functions cannot be
performed.
MBR The member of the file to process. The default is
*FIRST.
OPTION The type of open to use. This is the same parameter
as on OPNQRYF and defaults to *INP.
Note that this is the last positional parameter but
appears as the second parameter when prompting.
*ALL may be specified as a single value and means
that the file is open for all operations (*INP,
*OUT, *UPD, *DLT).
Up to four repetions of the following values may be
specified.
*INP Open the file for input. *INP is the only
value allowed if join processing or group processing
is requested, if UNIQUEKEY processing is specified,
if all the fields in the open query file record
format specified for the Format specifications
(FORMAT) parameter are for input-only use, or if a
temporary file is required to run the query.
*OUT Open the file for output. In some high-level
languages, output to certain files (such as files
defined as 'direct access' in the high-level
language program) is done by using a combination of
input and update operations. *UPD and *INP are
specified, or *ALL is specified, in order to use an
open query file with such a program.
*UPD Open the file for update operations. If an
input operation comes before an update, you must
specify *INP when *UPD is specified.
*DLT Open the file for delete operations. If a
delete operation is preceded by an input operation,
you must specify *INP when *DLT is specified.
BLDQRYSLT command *CMD
-----------------
The BLDQRYSLT command builds the variable to be used in an OPNQRYF
command. BLDQRYSLT can only be used in a CL program.
The BLDQRYSLT command parameters are:
QRYSLT The return variable name to be used. It must be
defined as a 2000 byte character field.
SELECT The selection criteria. Up to 35 different
selection statements can be made. It has the same
definition as the QRYF command.
Prerequisites
-------------
The following TAA Tools must be on your system:
EXTLST Extract list
SNDESCMSG Send escape message
Implementation
--------------
None, the tool is ready to use.
Objects used by the tool
------------------------
Object Type Attribute Src member Src file
------ ----- --------- ---------- -----------
QRYF *CMD TAAQRYA QATTCMD
BLDQRYSLT *CMD TAAQRYA2 QATTCMD
TAAQRYAC *PGM CLP TAAQRYAC QATTCL
TAAQRYAC2 *PGM CLP TAAQRYAC2 QATTCL
Structure
---------
QRYF
TAAQRYAC CL pgm
TAAQRYAC2 CL pgm
BLDQRYSLT
TAAQRYAC2 CL pgm
|
