Tags:
create new tag
view all tags

Accessing Query Parameters


Overview:

The Query specifications used to produce a report are stored in three memory files, defined in the --- application.

You can find the current data dictionary definitions of these files below. Note that these files may change without notice in the future and you should be prepared to lose any functionality that you add.

Each file can contain records for multiple active query processes. The primary key of each file contains the application ID and process name of the query.

These files are populated when you run a query process, in query setup phase. They are read when the query is actually evaluated. A query setup process populates the Q-files based on the specifications entered by the query designer.

It is possible to change the contents of the Q-files after the query setup processor has populated them. This is a dangerous practice and you are on your own if you do, but it can be useful. Two important rules apply if you decide to twiddle the Q-files:

1) You will probably break something in an unpredictable way if you make a mistake.

2) You cannot introduce a reference to file or work field that was not already referenced by the query. This means that if you think you might need to add a selection constraint or sort ordering, you have to mention that file (or a field from that file) or that work field somewhere in the query process. A simple do-nothing statement will suffice (such as RESTORE DEFAULT RECORD).

If you want to edit the QSLCT records after query setup (Sort, and Designer & Enduser Selection at Runtime) has populated them, try it in "End of Process (Query Setup)". If you wait until the Start of Query Execution, the OUTPUT or UPDATE processor has already read the QLSCT records, so any changes that you might make will have no effect.

Each of the Q-files can be divided into two sets of fields: fields which control the result set of the query (i.e. the sort order and selection criteria), and fields which control the query setup phase.

Here is a quick description of interesting fields in each of the files:

<a name="qparm"></a>QPARM

QPARM QRY EXPR DSPL contains the selection expression seen by the user.

QPARM QRY EXPR HIDE contains the hidden selection expression (the "Designer Selections").

Note that these expressions may contain references to criteria which are not actually used. For example, your selection expression might be "100 and 200", but if constraint 200 is not active (ie. the right-hand side is blank), the query processor will effectively trim off the "and 200" part of the selection expression.

QSLCT

Each QSLCT record contains two operands (the left and right hand fields), one relation ( EQ, GT, LE, IN, etc.), and some extra information.

The operands are stored in the group fields QSLCT OPND L and QSLCT OPND R (each contains an application ID, field name, and occurrence number).

The relation is stored in QSLCT REL.

The QSLCT ACTV field can be examined to determine whether a constraint is actually going to be used. A constraint is active if the relation is non-null and the right-hand operand is also non-null.

Don't forget that if your goal is to print selection criteria on the report you will have to somehow account for records excluded (or included) by ILF code.

QSORT

Each QSORT record specifies an ordering segment. The sort field is identified by the QSORT FLD AP, QSORT FLD NAM, and QSORT FLD OCC parameters.

QSORT ORD determines whether an ordering segment will be ascending (+) or descending (-).

QSORT ACTV tells you whether a QSORT record is active.

QSORT SUBH controls whether a subheading will be executed when the value of the sort field changes, and whether the subheading will generated a form-feed.

QSORT SUBT and QSORT SUBT NEW PG determine whether a subtotal will be executed when the value of the sort field changes, and whether that subtotal will generate a form-feed.


Below you will find detailed Technical Documentation for the Q-files. The fields in the tech doc below, and not described in the descriptions above, either control the query setup process, or are not implemented.


                                                               -Options--
File     Descriptive Name         Org     File Type             T DP AA RP
======== ======================== ======= ===================== ==========
 *QPARM    Query Specification*  INDEXED MEMORY-RELATED        T

Seq No  Field Name             Typ Format       S Start   Len   T DL AA KC
------- ---------------------- --- ------------ - ----- -----   ----------
100     QPARM KEY              Hdr                    1    33   T       P
200     QPARM QRY AP           Dom X(3)               1     3
300     QPARM QRY NAM          Dom X(30)              4    30
400     QPARM KEY TRAIL        Trl                              T
500     QPARM ALIGN 1          Dom X(3)              34     3
600     QPARM LAST DFLT ORDER  Dom 9(10)             37     4        AA
700     QPARM QRY EXPR ED      Dom y/n               41     1
800     QPARM QRY EXPR DSPL    Dom X(800)            42   800
900     QPARM QRY EXPR HIDE    Dom X(800)           842   800
1000    QPARM QRY VER          Dom X(2)            1642     2
1100    QPARM ALIGN 2          Dom X(1)            1644     1 



                                                                -Options--
File     Descriptive Name         Org     File Type             T DP AA RP
======== ======================== ======= ===================== ==========
 *QSLCT    Selection Specs*       INDEXED MEMORY-RELATED        T    AA

Seq No  Field Name             Typ Format       S Start   Len   T DL AA KC
------- ---------------------- --- ------------ - ----- -----   ----------
100     QSLCT KEY              Hdr                    1    40   T       P
200     QSLCT PARTIAL KEY 1    Hdr                    1    34   T
300     QSLCT PARTIAL KEY 2    Hdr                    1    33   T
400     QSLCT QRY AP           Dom X(3)               1     3
500     QSLCT QRY NAM          Dom X(30)              4    30
600     QSLCT PARTIAL KEY 2 TR Trl                              T
700     QSLCT TYPE             Dom X(15), Tbl        34     1
800     QSLCT PARTIAL KEY 1 TR Trl                              T
900     QSLCT PAD 1            Dom X(2)              35     2
1000    QSLCT SNO              Dom 9(10)             37     4        AA
1100    QSLCT OPND L           Hdr                   41    30   T
1200    QSLCT OCC L CNST       Dom 9(3)              41     2        AA
1300    QSLCT AP ID L          Dom X(3)              43     3
1400    QSLCT FLD NAM L        Dom X(22)             46    22
1500    QSLCT PAD 2            Dom X(3)              68     3
1600    QSLCT OPND R           Hdr                   71    35   T
1700    QSLCT OCC R CNST       Dom 9(3)              71     2        AA
1800    QSLCT AP ID R          Dom X(3)              73     3
1900    QSLCT REF NAM R        Dom X(30)             76    30
2000    QSLCT OPND R TRAIL     Trl                              T
2100    QSLCT REL              Dom X(2), Tbl        106     1
2200    QSLCT ACTV             Dom y/n              107     1
2300    QSLCT MOD              Dom y/n              108     1
2400    QSLCT REL ED           Dom y/n              109     1
2500    QSLCT OPND R ED        Dom y/n              110     1
2600    QSLCT ALIGN 1          Dom X(2)             111     2 



                                                                -Options--
File     Descriptive Name         Org     File Type             T DP AA RP
======== ======================== ======= ===================== ==========
 *QSORT    Sort Order Specs*     INDEXED MEMORY-RELATED        T    AA

Seq No  Field Name             Typ Format       S Start   Len   T DL AA KC
------- ---------------------- --- ------------ - ----- -----   ----------
100     QSORT RECORD           Hdr                    1    92   T
200     QSORT KEY              Hdr                    1    40   T       P
300     QSORT PARTIAL KEY      Hdr                    1    33   T
400     QSORT QRY AP           Dom X(3)               1     3
500     QSORT QRY NAM          Dom X(30)              4    30
600     QSORT PARTIAL KEY TRAI Trl                              T
700     QSORT PAD              Dom X(3)              34     3
800     QSORT SNO              Dom 9(10)             37     4        AA
900     QSORT KEY TRAIL        Trl                              T
1000    QSORT FLD AP           Dom X(3)              41     3
1100    QSORT FLD NAM          Dom X(22)             44    22
1200    QSORT ALIGN 1          Alp X(1)              66     1
1300    QSORT FLD OCC          Dom 9(3)              67     2        AA
1400    QSORT ORD              Dom X(1), Tbl         69     1
1500    QSORT REQ              Dom y/n               70     1
1600    QSORT NEW PG           Dom y/n               71     1
1700    QSORT SUBH             Dom y/n               72     1
1800    QSORT CONT MSG         Dom y/n               73     1
1900    QSORT SUBT             Dom y/n               74     1
2000    QSORT SUBT NEW PG      Dom y/n               75     1
2100    QSORT AVE              Dom y/n               76     1
2200    QSORT MIN              Dom y/n               77     1
2300    QSORT MAX              Dom y/n               78     1
2400    QSORT ORD ED           Dom y/n               79     1
2500    QSORT NEW PG ED        Dom y/n               80     1
2600    QSORT SUBH ED          Dom y/n               81     1
2700    QSORT CONT MSG ED      Dom y/n               82     1
2800    QSORT SUBT ED          Dom y/n               83     1
2900    QSORT SUBT NEW PG ED   Dom y/n               84     1
3000    QSORT AVE ED           Dom y/n               85     1
3100    QSORT MIN ED           Dom y/n               86     1
3200    QSORT MAX ED           Dom y/n               87     1
3300    QSORT ACTV             Dom y/n               88     1
3400    QSORT MOD              Dom y/n               89     1
3500    QSORT ALIGN 2          Dom X(3)              90     3
3600    QSORT RECORD TRAIL     Trl                              T 
                                                                -Options--
File     Descriptive Name         Org     File Type             T DP AA RP
======== ======================== ======= ===================== ==========
 *QTAG     Query Tag File*        INDEXED MEMORY-RELATED        T

Seq No  Field Name             Typ Format       S Start   Len   T DL AA KC
------- ---------------------- --- ------------ - ----- -----   ----------
100     QTAG KEY               Hdr                    1    40   T       P
200     QTAG QRY AP            Dom X(3)               1     3
300     QTAG QRY NAM           Dom X(30)              4    30
400     QTAG ALIGN             Dom X(3)              34     3
500     QTAG SNO               Dom 9(10)             37     4        AA
600     QTAG RECORD            Hdr                   41  1024   T
700     QTAG KEY VAL           Dom X(1024)           41  1024
800     QTAG RECORD TRAILER    Trl                              T



Here's another way of looking at it (from ECR #2783):

How might one report on the contents of --- QSORT, --- QSLCT, and --- QPARM in an OUTPUT start-of-Report frame?

(These APPX tables contain QUERY Sort and Record Selection information, for a particular execution of a QUERY/OUTPUT or QUERY/UPDATE.)

You can access each of these files within ILF code and also within screen painting by using the SCAN key to get the actual fields within these 3 files.

Designer and Enduser Selections both come out of the --- QSLCT file, at runtime. (You can scan on them when editing a SET stmt by setting the Application-ID to "---", pressing SCAN, and giving it a filename/SeqNo of "QSLCT/100".)

A couple of the QSLCT fields of interest are:

   QSLCT    2200    QSLCT ACTV             DOMAIN            y/n 
/*    Is this an "Active" Record Selection (at run time)?       */ 
 
   QSLCT    2300    QSLCT MOD              DOMAIN            y/n 
/*    Is this "Modifiable"?   (Designer=N, Enduser=Y)           */ 

Example of accessing Enduser Sort Criteria:

One might put this ILF into a REPORT START frame, with Sort and Selection criteria painted in multiple rows, displayed here by 'Appearance #', and with "TRAILING BLANK LINES" compressed out.

SET      --- II                         =      0 
 
BEG READ --- QSORT     HOLD 0              KEY IS  QSORT KEY 
COMPUTE  --- II                         +      1 
 
DISPLAY  --- QSORT SNO                  (AT APPEARANCE # II ) 
DISPLAY  --- QSORT FLD NAM              (AT APPEARANCE # II ) 
DISPLAY  --- QSORT ACTV                 (AT APPEARANCE # II ) 
 
END READ --- QSORT 

Example of accessing Record Selection Criteria:

SET      --- II                         =      0 
BEG READ --- QSLCT     HOLD 0              KEY IS  QSLCT KEY 
COMPUTE  --- II                         +      1 
 
DISPLAY  --- QSLCT SNO                  (AT APPEARANCE # II ) 
DISPLAY  --- QSLCT REL                  (AT APPEARANCE # II ) 
DISPLAY  --- QSLCT ACTV                 (AT APPEARANCE # II ) 
DISPLAY  --- QSLCT MOD                  (AT APPEARANCE # II ) 
DISPLAY  --- QSLCT FLD NAM L            (AT APPEARANCE # II ) 
 
END READ --- QSLCT 

When editing an ILF SET statement, you can scan on valid field names in the QSLCT table by setting the Application-ID to "---", then pressing SCAN, giving it a starting Filename/SeqNo of "QSLCT/100".

QUERY PROCESS

Here is an example of printing the Query parameters on the report, given the following query:


Sort Order

                                     Range-Start/      Rng-End/ Lst  Srt
Seq No  App Field Name           Occ Subheading        SubTotal Lvl? Ordr
======= === ==================== === ================= ======== ==== ====
100     OPT BB KEY                   NO                NONE     Y     + 
200     OPT BB DATE                  NO                NONE     N     + 

___________________________________________________________________

Enduser Selections

Seq No  App Field Name            Occ    App Field Name or Constant 
======= === ===================== === == === ========================== 
100     OPT BB KEY                    GE 
200     OPT BB KEY                    LE     9 
_______________________________________________________________________ 

Designer Selections

Seq No  App Field Name             Occ    App Field Name or Constant 
======= === ====================== === == === ========================== 
100     OPT BB KEY                     NE     4321 
200     OPT BB DATE                    NE 
300     OPT BB ALPHA                   IN 
_______________________________________________________________________ 

OUTPUT Pre-Display ILF SERIES:

      TRAP 
      SET      --- II                         =      0 
      BEG READ --- QSORT     HOLD 0              KEY IS  QSORT KEY 
      COMPUTE  --- II                         +      1 
      DISPLAY  --- QSORT SNO                  (AT APPEARANCE # II ) 
      DISPLAY  --- QSORT FLD NAM              (AT APPEARANCE # II ) 
      DISPLAY  --- QSORT ACTV                 (AT APPEARANCE # II ) 
      END READ --- QSORT 
      SET      --- II                         =      0 
      TRAP 
      BEG READ --- QSLCT     HOLD 0              KEY IS  QSLCT KEY 
      COMPUTE  --- II                         +      1 
      DISPLAY  --- QSLCT SNO                  (AT APPEARANCE # II ) 
      DISPLAY  --- QSLCT REL                  (AT APPEARANCE # II ) 
      DISPLAY  --- QSLCT ACTV                 (AT APPEARANCE # II ) 
      DISPLAY  --- QSLCT MOD                  (AT APPEARANCE # II ) 
      DISPLAY  --- QSLCT FLD NAM L            (AT APPEARANCE # II ) 
      END READ --- QSLCT 

Results, painted into OUTPUT's Report-start frame:

           Fld Name              Ord  Actv 
         0                        +    Y 
         1 BB KEY                 +    Y 
         2 BB DATE                +    N 
 
           Type            Rel  Actv  Mod  FLD NAM L 
       100 QUERY-SLCT-HIDE  GE   N         BB KEY 
       200 QUERY-SLCT-HIDE  LE   Y         BB KEY 
       100 QUERY-SLCT-HIDE  NE   Y     N   BB KEY 
       200 QUERY-SLCT-HIDE  NE   Y     N   BB DATE 
       300 QUERY-SLCT-HIDE  IN   Y     N   BB ALPHA 
 
Bb  Date     Alpha                           K 
Key                 Num 9 2_      T          ? 
=== ======== ====== ============= ========== = 
0                   .00                      N 
1            one    1234.56       TOKEN-THREE 
2            Two    1.00-         TOKEN-THREEN 
3            Three. 2.00-         TOKEN-THREEN 
4            four   .00           TOKEN-THREEN 
5            Five   12345.67      TOKEN-TWO  N 
6   08/23/95 six    12345.12      TOKEN-THREEN 
7   08/23/95 seven  1234.12       TOKEN-TWO  N 
8   08/23/95 eight  1.23                     N 
9   11/11/11 nine   9.09          TOKEN-ONE  N 

Comments:

Read what other users have said about this page or add your own comments.



Edit | Attach | Watch | Print version | History: r2 < r1 | Backlinks | Raw View | Raw edit | More topic actions
Topic revision: r2 - 2016-02-18 - JeanNeron
 
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2024 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback