TAA Tools
QRYF       FRONT END TO OPNQRYF                      TAAQRYA

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
					

Added to TAA Productivity tools April 1, 1995


Home Page Up to Top