Exporting a prompted query

An exported prompted query object contains the information displayed in the echo area of the Prompted Query primary panel.

A sample query (before export)

A data set or data queue that contains an exported prompted query can be imported into the QMF in two ways. The data set or data queue can either be imported into the QMF temporary storage area or directly into the database. When you import a prompted query, QMF checks whether the incoming query is consistent with the data in the database. For example, if the prompted query that is imported has columns A, B, and C in table XYZ, QMF verifies that table XYZ with columns A, B, and C exists in the database.

This example shows sample echo text that appears on the Prompted Query primary panel before exporting.

  Tables:
    Q.STAFF(A)
    Q.ORG(B)
    Q.STAFF(C)
 
  Join Tables:
    A.DEPT And B.DEPTNUMB
    And A.ID And C.ID
 
  Columns:
    A.ID
    A.DEPT
    A.JOB
    A.SALARY
    DEPTNUMB
    C.SALARY
    C.SALARY+A.COMM
 
  Row Conditions:
    If A.SALARY Is Greater Than 10000
    And A.DEPT Is Equal To 84 or 96
 
  Sort:
    Descending by C.SALARY+A.COMM
 
  Duplicate Rows:
    Keep duplicate rows

How the exported query looks

This example shows the format of the exported prompted query.

H QMF 20 T 01 E V W E R 01 03 13/01/15 16:20
T 1110 003 002 1112 001 1113 050
R A Q.STAFF
R B Q.ORG
R C Q.STAFF
T 1150 002 002 1152 020 1153 020
R A.DEPT               B.DEPTNUMB
R A.ID                 C.ID
T 1210 007 002 1212 001 1213 255
R C A.ID
R C A.DEPT
R C A.JOB
R C A.SALARY
R C B.DEPTNUMB
R C C.SALARY
R C C.SALARY+A.COMM
T 1310 009 003 1312 001 1313 008 1314 255
R 1 C        A.SALARY
R 2 IS       GT
R 3          10000
R 4 I
R 1 C        A.DEPT
R 2 IS       EQ
R 3          84
R 3          96
R 4 A
T 1410 001 002 1412 001 1413 255
R D C.SALARY+A.COMM
V 1501 001 K
E

Interpreting the header record in the exported data set or queue

This following table shows the meaning of this header record in the exported prompted query shown in How the exported query looks.

H QMF 20 T 01 E V W E R 01 03 13/01/15 16:20

Table 1. Example of a prompted query header record
Value from example	Description
`H QMF 19 T`	This prompted query header record specifies QMF V12.1.
`01`	The structure of the prompted query is at object level 1. If the exported query object contains a period specification, object level 2 is specified instead.
`E`	The format is encoded (the format used for exported forms, reports, and prompted queries).
`V`	The exported prompted query does not contain any errors or warnings.
`W`	The file contains the entire prompted query.
`E`	English was the national language in use when the object was exported.
`R`	When importing, the object in the temporary storage area is replaced.
`01`	The length of the control area is 1 byte.
`03`	The length of integer length fields is 3 bytes.
`13/01/15`	The date stamp specifies January 15, 2013.
`16:20`	The timestamp specifies a time of 4:20 PM.

See How the exported query looks for a complete example of the Prompted Query encoded format.

Interpreting the records of the exported prompted query

Table definitions (field number 1110) are always exported. Join conditions (field number 1510) are always exported if more than one table is selected.

To import a prompted query file, the file must have an H record followed by the T record of the encoded table. If no tables are specified, an empty query is imported. Join conditions are not required unless more than one table is selected.

Table 2. Table and field numbers for an exported prompted query object
Record type	Table number	Field number	Field description
T	1110	-	Table definitions table The T record in this section of the exported prompted query in How the exported query looks identifies this section as the portion that contains the table names involved in the query: `T 1110 003 002 1112 001 1113 050` '003' refers to 3 tables, while '002' refers to 2 field numbers (1112 and 1113). If the exported query object contains a period specification, a value of '003' is used to indicate 3 field numbers (1112, 1113, and 1114) instead. Each T record is followed by R records and, in this example, the R records identify the tables involved in the prompted query join: `R A Q.STAFF R B Q.ORG R C Q.STAFF` This portion of the exported file corresponds to the following part of the prompted query shown in #dsq_expqry__ASampleQuerybeforeExport: `Tables: Q.STAFF(A) Q.ORG(B) Q.STAFF(C)`
		1112	Table ID (valid table IDs are A-Z, and #,$,@)
		1113	Table name (maximum of 280 characters)
		1114	Period specification (maximum of 560 characters). This field number is included only if the exported query object contains a period specification.
T	1150	-	Join conditions table The T record in this section of the exported prompted query in How the exported query looks identifies this section as the portion that contains the join conditions involved in the query. Each T record is followed by R records that identify which tables will be joined: `T 1150 002 002 1152 020 1153 020 R A.DEPT B.DEPTNUMB R A.ID C.ID` This portion of the sample exported query corresponds to the following part of the sample prompted query shown in #dsq_expqry__ASampleQuerybeforeExport: `Join Tables: A.DEPT And B.DEPTNUMB And A.ID And C.ID`
		1152	Column 1 name: Short length (22) Expanded length (34)
		1153	Column 2 name: Short length (22) Expanded length (34)
T	1210	-	Columns table The T record in this section of the exported prompted query in How the exported query looks identifies this section as the portion that contains the column names involved in the query. Each T record is followed by R records that identify the column names. The section appears as follows in the exported query: `T 1210 007 002 1212 001 1213 255 R C A.ID R C A.DEPT R C A.JOB R C A.SALARY R C B.DEPTNUMB R C C.SALARY R C C.SALARY+A.COMM` This section of the exported query corresponds to the following section of the sample query shown in #dsq_expqry__ASampleQuerybeforeExport: `Columns: A.ID A.DEPT A.JOB A.SALARY DEPTNUMB C.SALARY C.SALARY+A.COMM`
		1212	Column type: C=column E=expression S=summary function with expression F=summary function with only a column
		1213	Column name, expression, or summary function: Short length (255) Expanded length (560)
T	1310	-	Row selection conditions The T record in this section of the exported prompted query in How the exported query looks identifies this section of the exported query as the portion that contains the query conditions. Each T record is followed by R records that characterize each condition. The section appears as follows in the exported prompted query: `T 1310 009 003 1312 001 1313 008 1314 255 R 1 C A.SALARY R 2 IS GT R 3 10000 R 4 I R 1 C A.DEPT R 2 IS EQ R 3 84 R 3 96 R 4 A` This section of the exported query corresponds to the following section of the query shown in #dsq_expqry__ASampleQuerybeforeExport: `Row Conditions: If A.SALARY Is Greater Than 10000 And A.DEPT Is Equal To 84 or 96`
		1312	Entry type: 1 - left of operator 2 - operator 3 - right of operator 4 - connector
		1313	For entry type '1', identifies column type: C=column E=expression S=summary function F=summary function (column name only specified)
			For entry type '2', identifies the verb: IS for 'is' (default) ISN for 'is not'
			For entry type '3' (not used)
			For entry type '4', identifies a connector: O for 'or' A for 'and' (default)
		1314	For entry type '1' this field is a column name, expression, or summary function: Short length (255) Expanded length (560)
			For entry type '2', identifies the operator: EQ for 'equal to' LT for 'less than' LE for 'less than or equal to' GT for 'greater than' GE for 'greater than or equal to' BT for 'between' SW for 'starting with' EW for 'ending with' CT for 'containing' NL for NULL
			For entry type '3', identifies a value
			For entry type '4' (not used)
T	1410	-	Sort conditions table The T record in this section of the exported prompted query in How the exported query looks identifies this section as the portion that contains the sort conditions for the query. Each T record is followed by R records that characterize each sort condition. This section appears as follows in the exported prompted query: `T 1410 001 002 1412 001 1413 255 R D C.SALARY+A.COMM` This section of the exported query corresponds to the following section of the sample query in #dsq_expqry__ASampleQuerybeforeExport: `Sort: Descending by C.SALARY+A.COMM`
		1412	Sort direction: A for 'ascending' D for 'descending'
		1413	Column: Short length (255) Expanded length (560)
V		1501	Treatment of duplicate rows: K for 'keep' D for 'discard' For example, the following line in the sample exported prompted query in How the exported query looks shows that the record length of the value K is 1 ("001"). The line also shows that the user who built the query specified to keep duplicate rows ("K"): `V 1501 001 K` This section of the exported query corresponds to the following section of the sample query shown in #dsq_expqry__ASampleQuerybeforeExport: `Duplicate Rows: Keep duplicate rows`

The meaning of values for fields 1313 and 1314 depends on the sequence number indicated in field number 1312 in table number 1310.