SELECT Command optional clauses

Edit online

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 that are used to define the layout of the output are described in the Formatting section.

ORDER BY clause

ORDER BY specifies that data selected for output be sorted into ascending (“A”) or descending (“D”) order. With an ORDER BY clause, ordering is ascending unless “D” is specified. The field list can only name fields specified in the SELECT statement. If no ORDER BY clause is specified, the output is in the same order as the input.

The non-standard SQL extension, DISTINCT, will eliminate duplicate rows having the same values specified in the ORDER BY fields. Example:

SELECT BIRTHYEAR, FIRSTNAME, LASTNAME, ZIPCODE
	FROM MYUID.STAFF
	WHERE STATE = 'WA'
	ORDER BY BIRTHYEAR D, LASTNAME, FIRSTNAME, ZIPCODE
	CREATE MYUID.WA.REPORT

GROUP BY clause

GROUP BY field

GROUP BY specifies that data select for output be summarized into output records. The field list can only name fields specified in the SELECT statement.

When GROUP BY is used you group by all SELECT fields that are not aggregate functions. The output will have one row for each unique combination of those grouped fields. Example:

SELECT COUNT(*), BIRTHYEAR, ZIPCODE
	FROM MYUID.STAFF
	WHERE STATE = 'WA'
	GROUP BY BIRTHYEAR, ZIPCODE
	ORDER BY BIRTHYEAR 
	CREATE MYUID.WA.REPORT

HAVING clause

HAVING is used with GROUP BY to select only those summarized rows whose aggregate functions on a field meet a condition. Example:

SELECT VOLSER, COUNT(*)
	FROM MY.EXPLORE.EXTRACT
	GROUP BY DSTYPE
	HAVING COUNT(*) > 1 
	CREATE MYUID.VOLSER.REPORT

JOIN clause

The JOIN clause names a second data source that will be merged with the data source from the FROM … USING clauses.

INNER: (Default). This join type matches only those records of the two data sources with matching ON fields. All other records are ignored
RIGHT OUTER: This join type selects all records from the right table (the second data source specified in the FROM part of the JOIN clause), and match them with only those records of the left table (the first data source named in the SELECT … FROM clause) that have the ON fields. All other left table records are ignored.
LEFT OUTER: This join type selects all records from the left table (the first data source named in the SELECT … FROM clause), and match them with only those records of the right table (the second data source specified in the FROM part of the JOIN clause) that have the ON fields. All other right table records are ignored.
FULL OUTER: This join type selects all records from both data sources and match them with only those records of the left table that have the ON fields. All matched records and all unmatched records are selected.

JOIN table

The JOIN data source definition describes the second data source that will be merged with the data source from the FROM clause. See the FROM clause definition above for the operands of the JOIN.

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')

ON

The ON clause names a field from the first data source and a field from the second data source to be used to match records during the join.

Table aliases are not required nor allowed. The field name on the left must be defined by the first data source and the field name of the right must be defined by the second data source. Example:

ON DSNAME = DSNAME

USING clause

The USING clause defines where the data source's layout is defined. See the USING clause definition above.

FORMAT OPTIONS

The FORMAT OPTIONS and FORMAT FOLLOWS clauses direct MQFL to use a user-specified output formatting as described in the MQFL Formatting section.

If user-specified formatting is not specified, MQFL will write the output as follows:

If SELECT * is specified, the selected input data will be written in the same format as the input with all fields present.
If SELECT *DUMP is specified, the selected input data will be written in a hex-dump format.
Otherwise, MQFL will write the selected input data in a form that subsequent MQLF SELECT statement can use. This form has a self-defining embedded dictionary that precedes the data.

FROMKEY clause

FROMKEY 	fieldname = value

FROMKEY specifies that input records before this value will be ignored. For character keys, the value specified can be shorter than the field length and will match the prefix of the specified field. In the case of a KSDS data set, the specified key value is used to in a POINT to the location in the file to start reading if the field name specified matches the relative key position data set.

The use of FROMKEY and TOKEY may simplify the specification of the WHERE clause. FROMKEY and TOKEY can be used independently.

FROMKEY Example

In this example, the processing begins at the first record that starts with DCD. It finishes after passing the last record containing DCD.

SELECT MCDDSN, MCDVSN, MCDTLR, MCDDMIG 
  FROM (RLS1.HSM.SPLIT.MCDS1 RLS1.HSM.SPLIT.MCDS2) 
USING DCD.MFL.MAPS(MCD) 
CREATE DCD.HSM.MCDPLAN1.FORMAT 
FROMKEY MCDDSN = 'DCD.' 
TOKEY   MCDDSN = 'DCD.' 
WHERE MCHTYPE = X'00' 
   AND MCDFLGS ON B'10000000' 
ORDER BY MCDDSN A

TOKEY clause

TOKEY 	fieldname = value

After the key value specified in the argument is passed, processing stops. TOKEY specifies that input records after this value will be ignored. For character keys, the value specified can be shorter than the field length and will match the prefix of the specified field.

The use of FROMKEY and TOKEY may simplify the specification of the WHERE clause. FROMKEY and TOKEY can be used independently.

WHERE clause

MQFL evaluates the WHERE clause to either true or false. Input data evaluated to true will be selected. Omitting the WHERE clause indicates that all input data will be used in the output.

The WHERE clause consists of the word WHERE followed by a Boolean expression consisting of a simple or a complex predicate. Simple predicates are defined below. Compound predicates use parentheses, AND, or OR in a logical expression to test more conditions.

WHERE Single Predicate Examples

WHERE SGROUP = 'HSMML1' 
WHERE DSNAME LIKE 'MYDSN.%' 		
WHERE DSNAME ACSLIKE 'MYDSN.**' 
WHERE EXTENTS > 1 
WHERE MIGDATE > *+7 
WHERE MIGDATE > 2009200 		
WHERE MIGDATE > BACKDATE 
WHERE VOLSER IN (‘PRD001', ‘PRD002')		
WHERE BLKSIZE IN (80, 132, 133)
WHERE DSNFLAGS ON B'10000000'	or  WHERE DSNFLAGS ON X'80'

WHERE Compound Predicate Examples

WHERE MIGDATE > BACKDATE AND VOLSER = '123456' 
WHERE MIGDATE > BACKDATE OR MIGDATE > '*-5' 
WHERE MIGDATE = BACK DATE 
  AND VOLSER = '123456' 
  AND MIGDSN IN ('USERID.DATA.SET', 'ANOTHER.USER.DATA.SET')  
WHERE MIGDATE = BACKDATE
  AND NOT (VOLSER = '123456' OR EXTENTS = 1)

WHERE predicates formats:

Standard SQL syntax

WHERE field-name comparison-operator literal: This format compares a field with a literal. The field must be on the right side of the comparison operator.
WHERE field-name comparison-operator field-name: This format compares two fields.
WHERE field-name [NOT] IN ( literal-list ): This format compares the field with one or more values separated by commas that match the type of the field.
WHERE field-name [NOT] LIKE text-literal: This format tests if the pattern specified by the text-literal is in the field. A percent sign is used as a wildcard character that matches zero or more characters. For example, ‘TSO%' matches a field that begins with the characters ‘TSO'.
WHERE field-name IS [NOT] NUMERIC: This format tests a field to see if it contains a valid numeric value. With this test you can test character, character-numeric, and packed fields for valid numeric values. This test is invalid for binary fields because all values would be valid.
WHERE field-name IS [NOT] NULL: This format tests if the field exists in the data. A field may be defined by the data definition but not have a value when a LEFT OUTER JOIN or a RIGHT OUTER JOIN does not find a matching record. This test is only valid for a LEFT OUTER JOIN or a RIGHT OUTER JOIN.

Extended SQL syntax

WHERE field-name [NOT] ACSLIKE text-literal: This format tests if a 44-byte data set name field matches the extended ACS masking characters specified by the text-literal.
WHERE field-name ON literal: This format tests if the bits on in the byte-literal or single-byte byte-literal are on in the field.
WHERE field-name OFF literal: This format tests if the bits on in the byte-literal or single-byte byte-literal are off in the field.
WHERE field-name MIXED literal: This format tests if at least one of the bits on in the byte-literal or single-byte byte-literal are on in the field.

field-name

A field-name is an element defined in a files data definition. Field-names can only be used on the left-hand side of a predicate unless two fields are compared.

comparison-operator

Comparison-operations specify the criteria that the data must meet.

Table 1. Comparison operators
=	Equal
<>	Not equal
<	Less than
>	Greater than
<=	Less than or equal
>=	Greater than or equal

literal

MQFL literals represent a fixed data value. When a literal is used in a WHERE clause it must match the type of the field that it is being compared to.

Numeric

Numeric literals specify a positive integer value and consist only of one or more digits. Numeric literals can only be used in comparisons with numeric fields.

Example: 1234.

Text

Text literals specify a value, begin with a single quote character and are terminated by a single quote character.

In comparison other than LIKE or ACSLIKE with character fields that are longer than the text literal, the text literal is expanded with spaces on the right.

Text literals can only be used in comparisons with character fields.

Example: 'MYDSN.**'.

Byte

Byte literals specify a single byte of a field, begin with B followed by a single quote character, eight bits of 0 or 1, and are terminated by a single quote character.

Byte literals can be used only in comparisons with character fields and are provided for the ON, OFF, and MIXED comparison operators.

Example: X'10000000'.

Hex

Hex literals specify a hexadecimal value, begin with X followed by a single quote character, and are terminated by a single quote character.

Hex literals can only be used in comparisons with character fields.

Example: X'01234'.

Date

Date literals specify either a fixed Julian date using a numeric format or a date related to the current date. Date literals are only useful for date fields.

Julian dates have a four-digit year followed by three-digit day of the year. Julian date example: 2009020, the twentieth day of 2009.

Relative dates are written with an asterisk indicating today or an asterisk immediately followed by a plus or minus sign and a one to four digit number. For example, *-5 represents today minus five days.