SELECT Command required clauses

The SELECT command's query features, allow you to select both those fields and those records that you want and output along with the sort criteria based on one or more of those output fields. The SELECT command's formatting features are used to define the layout of the output are described in the Formatting section.

SELECT clause

The SELECT clause names the fields that are to be output.

DISTINCT

SELECT DISTINCT specifies that MQFL will output only unique records consisting of the fields named. Using DISTINCT will order the output by the fields named in an ascending order. You can't use DISTINCT in the SELECT clause and in the ORDER BY clause.

TOP n

SELECT TOP specifies that MQFL will output only the first n-number of records of the complete output.

*

SELECT * specifies that the all fields in the input will be selected as individual fields. If the FORMAT FOLLOWS is not used, the output data will have an embedded dictionary of all of the fields.

*ROW

SELECT *ROW specifies that the entire record will be selected as is and no embedded dictionary is added. SELECT *ROW can't be used for FORMAT FOLLOWS formatted output. ORDER BY can be used to specify a sort order.

*DUMP

SELECT *DUMP specifies that the entire record will be selected as is and printed in a hex-dump format. SELECT *DUMP can't be used for FORMAT FOLLOWS formatted output. ORDER BY can be used to specify a sort order.

field

The SELECT field list specifies a list of one or more field names from the data definition file specified in the USING clause. The field names are not case sensitive. The field specification can take the form of:

fieldname: The field name from the data definition.
tablequalifier.fieldname: A qualified field name which has the table alias specified in the “AS table-alias” followed by a period (“.”), followed by a field name from the data definition. Example: mytable1.DSNAME. Qualified names are necessary only for SELECT-JOIN where field names referenced occur in both of the data sources. The qualification resolves the ambiguity of which field and data source is requested.
field(HEX): Either a field name or a qualified field name followed by “(HEX)” that specifies that the data will be shown in hex format. Example: DSNAME (HEX).

Specifying an aggregate function for a field name or qualified field name

Aggregate functions are generally used with GROUP BY but can be used without a GROUP BY if the SELECT list names only aggregate functions. Other than COUNT, these functions only operate on numeric fields. Aggregate functions include:

COUNT(*): Returns number of records.
SUM(field): Returns the total of a field.
MIN(field): Returns the minimum value of a field.
MAX(field): Returns the maximum value of a field.
AVG(field): Returns the average value of a field.

FROM clause

NOSORT: For SELECT … JOIN only, NOSORT suppresses the default sort of this data source by the JOIN ON field

Use the DD dsn, dsn, or (dsn,dsn, …) format to identify the input data.

DD ddn

The DD keyword specifies the name of a DD statement in your MQFL JCL for your input data source. Example:

FROM DD INPUTDD

dsn

A fully qualified data set name, generation data set, or library(member-name) specifies the input data source. Examples:

FROM MYUID.STAFF

FROM MYUID.PEOPLE.G0003V00

FROM MYUID.PEOPLE(0)

FROM MYUID.PEOPLE(STAFFNE)

( dsn )

A single data set name or a list of data set names enclosed in parentheses that specifies the input data source. Each data name can be a fully qualified data set name, generation data set, or library(member-name). Examples:

FROM (MYUID.STAFFNW MYUID.STAFFSW 
MYUID.STAFFSE MYSTAFF.NE)

FROM (MYUID.PEOPLE(0) MYUID.PEOPLE(-1))

FROM (MYUID.PEOPLE(STAFFNE) MYUID.PEOPLE(STAFFSE))

AS table-alias

The AS clause names an alias for this data source. The AS feature of SQL, is used and is necessary only for a SELECT … JOIN when one or more of the field names of the first data source are also used in the second data source.

Example:

AS NEWVOL

If an alias for a data source is specified, then the field names can be qualified with the alias using alias.fieldname format to indicate the source of the field. For example in a WHERE clause:

AS NEWVOL

WHERE NEWVOL.VOLSER IN (‘TSO001', ‘TSO002')

USING clause

Examples:

USING MYUID.DATADEF
USING HLQ.CNTL(STGPSUM) 
USING FROMDATASET
USING DEFERRED

USING DD ddname

Specifies the DDNAME of a DD statement in your JCL that references a data set containing the field layout of the records in the input data source. This field layout is in the format of a COBOL data definition.

The SCKMPARM library has data definitions listed in the following table. If you use the SCKMCNTL(CKMJMQFL) JCL procedure, the indicated DD statements are defined so that you can specify the USING DD clause indicated.

Table 1. SCKMPARM library data definitions
SCKMPARM member	Output Source	USING DD
CKMPEXMQ	EXPLORE EXTFLAT or EXTFLATFILE extract output	USING DD USINGEX
CKMPDSMQ	EXPLORE DSNLISTFILE output	USING DD USINGDS
CKMPDVMQ	IDCAMS DCOLLECT output for volume data	USING DD USINGDV

USING dsn

Specifies the fully-qualified data set name containing the field layout of the records in the input data source. This field layout is in the format of a COBOL data definition.

USING FROMDATASET

Specifies that MQFL use the input data definition is embedded at the beginning of the input data source. A SELECT without a FORMAT FOLLOWS writes its output with a self-describing section in front of the data. USING FROMDATASET uses that data definition.

USING DEFERRED

Specifies that the data definition was explicitly or implicitly defined by a prior SELECT statement within the SYSIN. See “Usage: Specifying multiple MQFL commands”.

APPEND clause / CREATE clause / USE clause

You must have either an APPEND, a CREATE, or a USE clause to define the output target. APPEND adds onto a file, CREATE deletes and redefines, and USE overwrites a file.

Example:

CREATE HLQ.DVLP.STGP.TR01MODL.CSV

For a DD name other than SYSPRINT, if FORMAT OPTIONS specifies FB80 or SELECT *DUMP is specified, the RECFM must be F or FB. Otherwise, your JCL must specify a data set allocated as RECFM=VB, LRECL=8192, and BLKSIZE=23476.

For APPEND dsname, CREATE dsname(member), USE dsname, or USE dsname(member), the data set name must already exist and the RECFM must match or the JOB step will abend.

With CREATE DEFAULT, the data set name comes from the DSN= parameter of the //DEFAULTS DD statement, with the current date and time appended to the end of the data set name.

For CREATE dsname, the data sets are defined as RECFM=VB, LRECL=8192, and BLKSIZE=23476 unless FORMAT OPTIONS specifies FB80 or SELECT *DUMP is specified. There are defaults that MQFL uses, however, you can control the type of allocation units (CYLINDERS or TRACKS) and the number of units by creating and using the CKMMQFLD member in SCKMPARM. You can then change these allocation units as needed.