Difference: AccessingQueryParameters (1 vs. 2)

Revision 22016-02-18 - JeanNeron

Line: 1 to 1
 
META TOPICPARENT name="DeveloperTips"

Accessing Query Parameters

Changed:
<
<
>
>

 

Overview:

Changed:
<
<

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

>
>

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

 
Changed:
<
<

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.

>
>
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.
Line: 26 to 24
  Here is a quick description of interesting fields in each of the files:
Changed:
<
<

QPARM

>
>

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

  QPARM QRY EXPR DSPL contains the selection expression seen by the user.
Line: 57 to 55
 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.

Changed:
<
<

>
>

  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.
Changed:
<
<

-Options--
>
>

                                                               -Options--

 File Descriptive Name Org File Type T DP AA RP ==== ==================== === ================= ====== QPARM Query Specification INDEXED MEMORY-RELATED T
Line: 176 to 176
 700 QTAG KEY VAL Dom X(1024) 41 1024 800 QTAG RECORD TRAILER Trl T
Changed:
<
<


>
>


 

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

Line: 185 to 186
  (These APPX tables contain QUERY Sort and Record Selection information, for a particular execution of a QUERY/OUTPUT or QUERY/UPDATE.)
Changed:
<
<
You can access each of these files within ilf code and also within screen painting, using the SCAN key to get the actual fields within these 3 files.

>
>
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:

Deleted:
<
<
 
   QSLCT    2200    QSLCT ACTV             DOMAIN            y/n 
/*    Is this an "Active" Record Selection (at run time)?       */ 
 
   QSLCT    2300    QSLCT MOD              DOMAIN            y/n 

Changed:
<
<
/* Is this "Modifiable"? (Designer=N, Enduser=Y) */

>
>
/* Is this "Modifiable"? (Designer=N, Enduser=Y) */
 
Changed:
<
<

Example of accessing Enduser Sort Criteria:

>
>

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.
Changed:
<
<
SET      --- II                         =      0 

>
>
SET      --- II                         =      0 

  BEG READ --- QSORT HOLD 0 KEY IS QSORT KEY COMPUTE --- II + 1
Line: 214 to 211
  END READ --- QSORT

Changed:
<
<

Example of accessing Record Selection Criteria:

SET      --- II                         =      0 

>
>

Example of accessing Record Selection Criteria:

SET      --- II                         =      0 

 BEG READ --- QSLCT HOLD 0 KEY IS QSLCT KEY COMPUTE --- II + 1

Line: 229 to 226
  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".
Deleted:
<
<
 

QUERY PROCESS

Changed:
<
<

Process Name- 2783


Sort Order

>
>
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     + 
Deleted:
<
<
 ___________________________________________________________________

Enduser Selections

Line: 250 to 247
 200 OPT BB KEY LE 9 ___________________________________________________________________
Deleted:
<
<
 Designer Selections
Added:
>
>
 
Seq No  App Field Name             Occ    App Field Name or Constant 
======= === ====================== === == === ========================== 
100     OPT BB KEY                     NE     4321 

Line: 260 to 256
 300 OPT BB ALPHA IN ___________________________________________________________________
Deleted:
<
<
 OUTPUT Pre-Display ILF SERIES:
Added:
>
>
 
      TRAP 
      SET      --- II                         =      0 
      BEG READ --- QSORT     HOLD 0              KEY IS  QSORT KEY 

Line: 282 to 277
  DISPLAY --- QSLCT FLD NAM L (AT APPEARANCE # II ) END READ --- QSLCT
Deleted:
<
<
 Results, painted into OUTPUT's Report-start frame:
Added:
>
>
 
           Fld Name              Ord  Actv 
         0                        +    Y 
         1 BB KEY                 +    Y 

Revision 12012-02-29 - ChrisBrower

Line: 1 to 1
Added:
>
>
META TOPICPARENT name="DeveloperTips"

Accessing Query Parameters

Overview:

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


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:

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, 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

Process Name- 2783


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.



<--/commentPlugin-->
 
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