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.

 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

Last modified on November 19, 2014 © 1995, 2014 - TAA Tools, Inc.