Built-in functions

Built-in functions

These built-in functions are supported for an expression that is used to define a derived field on the MAPFLD parameter or for a complex selection operand specified on the QRYSLT or GRPSLT parameter.
A numeric argument is a numeric field, a numeric constant or a numeric expression. A string argument is a character field, a character string literal, or a string expression. Unless otherwise noted, all built-in functions allow expressions, including other built-in functions, to be used as arguments. For a field that appears in the record format identified by the FORMAT parameter, and that is also defined by an expression on the MAPFLD parameter, the expression result is calculated by using the attributes described in the following paragraphs. Then the resultant value is mapped to match the attributes of the field.
%ABSVAL (numeric-argument)

%ABSVAL accepts a numeric argument and returns the absolute value of the argument. The returned value has the same attributes as the argument, unless the argument is a *BIN2, in which case the returned value is a *BIN4.
The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. Arguments of these types can be specified either as fields or literal values. The returned value is a packed decimal number (*DEC) with 8 digits and 0 precision (date duration), 6 digits and 0 precision (time duration), or 20 digits and 6 precision (timestamp duration).

%ACOS (numeric-argument)

%ACOS accepts a numeric argument and returns the arc cosine of the argument, in radians. %ACOS and %COS are inverse operations.
The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. Arguments of these types can be specified either as fields or literal values. The returned value is a double-precision floating-point number (*FLT8).

%AND (string-argument ...)

%AND accepts two or more character or hexadecimal string arguments and returns a string that is the bit-wise 'AND' (logical and) of the arguments. This function takes the first argument string, ANDs it with the next string, and continues to AND each successive argument with the previous result. If an argument is encountered that is shorter than the previous result, it is padded on the right with blanks. The returned value is a string of type *HEX with the same length as the longest argument. If any of the arguments are variable-length, the maximum length is used as the length of the argument.

%ANTILOG (numeric-argument)

%ANTILOG accepts a numeric argument and returns the antilogarithm (base 10) of the argument. %ANTILOG and %LOG are inverse operations.
The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. Arguments of these types can be specified either as fields or literal values. The returned value is a double-precision floating-point number (*FLT8).

%ASIN (numeric-argument)

%ASIN accepts a numeric argument and returns the arc sine of the argument, in radians. %ASIN and %SIN are inverse operations.
The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. Arguments of these types can be specified either as fields or literal values. The returned value is a double-precision floating-point number (*FLT8).

%ATAN (numeric-argument)

%ATAN accepts a numeric argument and returns the arc tangent of the argument, in radians. %ATAN and %TAN are inverse operations.
The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. Arguments of these types can be specified either as fields or literal values. The returned value is a double-precision floating-point number (*FLT8).

%ATANH (numeric-argument)

%ATANH accepts a numeric argument and returns the hyperbolic arc tangent of the argument, in radians. %ATANH and %TANH are inverse operations.
The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. Arguments of these types can be specified either as fields or literal values. The returned value is a double-precision floating-point number (*FLT8).

%AVG (numeric-argument)

%AVG accepts a numeric argument and returns the average value of its argument for the group of records defined on the GRPFLD parameter. The argument must be a field name or an expression (not a literal). The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. If no records are selected, the result is the null value. Otherwise,

If the argument is fixed-point, the result is a packed decimal number (*DEC) with 31 total digits and the same number of integer digits as the argument.

If the argument is floating-point, the result is a double-precision floating-point number (*FLT8).

If the argument is date duration, time duration, or timestamp duration, the returned value is a packed decimal number (*DEC) with 31 digits and 0 precision (date duration), 31 digits and 0 precision (time duration), or 31 digits and 6 precision (timestamp duration).

%AVG is an aggregate function that is used for a nongrouping field in a query that uses the grouping function.

%CHAR (date/time-argument date/time-format)
%CHAR accepts a date/time argument and date/time format and returns the character representation of the argument using the specified format. The date/time argument can be a date, time, or timestamp field. The returned value is of type *CHAR and is tagged with the CCSID of the current job. The date/time format is optional. If specified, it must be one of the following formats:

EUR

European format

ISO

International Standards Organization format

JIS

Japanese Industrial Standard format

USA

United States format

If the format is not specified, the job default format is used.
Example:
OPNQRYF    FILE(library/file)
     GRPFLD(charfld)
     GRPSLT('charfld = %CHAR(timefld "USA")')
%COS (numeric-argument)

%COS accepts a numeric argument and returns the cosine of the argument. The argument must be specified in radians. %COS and %ACOS are inverse operations.
The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. Arguments of these types can be specified either as fields or literal values. The returned value is a double-precision floating-point number (*FLT8).

%COSH (numeric-argument)

%COSH accepts a numeric argument and returns the hyperbolic cosine of the argument. The argument must be specified in radians.
The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. Arguments of these types can be specified either as fields or literal values. The returned value is a double-precision floating-point number (*FLT8).

%COT (numeric-argument)

%COT accepts a numeric argument and returns the cotangent of the argument. The argument must be specified in radians.
The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. Arguments of these types can be specified either as fields or literal values. The returned value is a double-precision floating-point number (*FLT8).

%COUNT

%COUNT does not support any arguments. It returns the count of the number of records contained in the group of records defined on the GRPFLD parameter. The returned value is a 4-byte binary number (*BIN4) with 10 total decimal digits and no fraction digits. %COUNT is an aggregate function that applies only to a query that uses the grouping function.

%CURDATE

%CURDATE does not support any arguments. It obtains the current date based on a reading of the time-of-day clock. The returned value is of type *DATE. The format and separator are derived from the job attributes.

%CURSERVER

%CURSERVER does not support any arguments. If only non-distributed files are specified then it obtains the current server name (or RDB name) of the local system. If distributed files are specified then it obtains the current server name (or RDB name) of the COORDINATOR node. The returned value is of type variable-length character (*VCHAR) with a maximum length of 18.

%CURTIME

%CURTIME does not support any arguments. It obtains the current time based on a reading of the time-of-day clock. The returned value is of type *TIME. The format and separator are derived from the job attributes.

%CURTIMESTP

%CURTIMESTP does not support any arguments. It obtains the current timestamp based on a reading of the time-of-day clock. The returned value is of type *TIMESTP. The format and separator will be derived from the job attributes.

%CURTIMEZONE

%CURTIMEZONE does not support any arguments. It obtains the current time zone. The returned value is a packed decimal number (*DEC) with 6 digits and 0 precision.

%DATE (date/time-argument)
%DATE accepts a date/time argument and returns a date. The date/time argument can be a date or timestamp field, a character or hexadecimal field containing the external form of a date, a date literal, or a numeric field or literal value in the range 1 - 3,652,059. The returned value is of type *DATE. Example:
 OPNQRYF      FILE(library/file)
     QRYSLT(('%DATE(tstampfld) =
        "1989-10-23"'))
%DAY (date/time-argument)

%DAY accepts a date/time argument and returns the day part of the value. The date/time argument can be a date or timestamp field, a date duration or timestamp duration (field or literal), or a numeric field or literal. The returned value is of type *BIN4.
A numeric field argument must be defined as packed decimal (*DEC) with 8 digits and 0 precision for date duration or packed decimal (*DEC) with 20 digits and 6 precision for timestamp duration. A numeric constant argument must have 8 digits followed by a decimal point, or 14 digits followed by a decimal point and 6 digits.

%DAYS (date/time-argument)

%DAYS accepts a date/time argument and returns an integer representation of the date. The date/time argument can be a date or timestamp field, a character or hexadecimal field containing the external form of a date, or a date literal. The returned value is of type *BIN4.

%DIGITS (numeric-argument)

%DIGITS accepts a numeric argument and returns a character representation of its numeric value, not including the sign or a decimal point. The result is tagged with the CCSID of the current job. For example, %DIGITS (-1.5) returns the character string 15. The numeric argument must not be a floating point number.

%DURDAY (integer-argument)

%DURDAY accepts an integer argument and returns a labeled duration of days. The integer argument for this function can be a numeric expression, a field, or a literal.
This built-in function is allowed to stand by itself in the mapped-field-definition of the MAPFLD parameter, and is allowed as part of an arithmetic (addition or subtraction) expression with a date or timestamp field on the QRYSLT, GRPSLT, or MAPFLD parameters.

%DURHOUR (integer-argument)

%DURHOUR accepts an integer argument and returns a labeled duration of hours. The integer argument for this function can be a numeric expression, a field, or a literal.
This built-in function is allowed to stand by itself in the mapped-field-definition on the MAPFLD parameter, and is allowed as part of an arithmetic (addition or subtraction) expression with a time or timestamp field on the QRYSLT, GRPSLT, or MAPFLD parameters.

%DURMICSEC (integer-argument)

%DURMICSEC accepts an integer argument and returns a labeled duration of microseconds. The integer argument for this function can be a numeric expression, a field, or a literal.
This built-in function is allowed to stand by itself in the mapped-field-definition on the MAPFLD parameter, and is allowed as part of an arithmetic (addition or subtraction) expression with a timestamp field on the QRYSLT, GRPSLT, or MAPFLD parameters.

%DURMINUTE (integer-argument)

%DURMINUTE accepts an integer argument and returns a labeled duration of minutes. The integer argument for this function can be a numeric expression, a field, or a literal.
This built-in function is allowed to stand by itself in the mapped-field-definition on the MAPFLD parameter, and is allowed as part of an arithmetic (addition or subtraction) expression with a time or timestamp field on the QRYSLT, GRPSLT, or MAPFLD parameters.

%DURMONTH (integer-argument)

%DURMONTH accepts an integer argument and returns a labeled duration of months. The integer argument for this function can be a numeric expression, a field, or a literal.
This built-in function is allowed to stand by itself in the mapped-field-definition on the MAPFLD parameter, and is allowed as part of an arithmetic (addition or subtraction) expression with a date or timestamp field on the QRYSLT, GRPSLT, or MAPFLD parameters.

%DURSEC (integer-argument)

%DURSEC accepts an integer argument and returns a labeled duration of seconds. The integer argument for this function can be a numeric expression, a field, or a literal.
This built-in function is allowed to stand by itself in the mapped-field-definition on the MAPFLD parameter, and is allowed as part of an arithmetic (addition or subtraction) expression with a time or timestamp field on the QRYSLT, GRPSLT, or MAPFLD parameters.

%DURYEAR (integer-argument)
%DURYEAR accepts an integer argument and returns a labeled duration of years. The integer argument for this function can be a numeric expression, a field, or a literal.
This built-in function is allowed to stand by itself in the mapped-field-definition value on the MAPFLD parameter, and is allowed as part of an arithmetic (addition or subtraction) expression with a date or timestamp field on the QRYSLT, GRPSLT, or MAPFLD parameters. Example:
OPNQRYF    FILE((library/file))
   QRYSLT('startfld > %CURDATE + oneyear *AND            endfld   < %CURDATE + %DURYEAR(2)')
   MAPFLD((oneyear '%DURYEAR(1)'))
%EXP (numeric-argument)

%EXP accepts a numeric argument and returns a value that is the base of the natural logarithm (e) raised to a power specified by the argument. %EXP and %LN are inverse operations.
The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. Arguments of these types can be specified either as fields or literal values. The returned value is a double-precision floating-point number (*FLT8).

%HASH (expression-argument)
%HASH accepts a valid expression and returns a 4-byte binary number (*BIN4) with 10 total decimal digits and no fraction digits. The returned value will be the partition number of the record selected.
A valid expression cannot include aggregate functions such as %COUNT, %AVG, %MIN, %MAX, %SUM, and %STDDEV as operands to %HASH.
Use the %HASH function to determine what the partitions are if the partitioning key is composed of EMPNO and LASTNAME. The following example returns the partition number for every row in EMPLOYEE. Example:
OPNQRYF      FILE((CORPDATA/EMPLOYEE))
     FORMAT(FNAME)
     MAPFLD((HASH '%HASH((1/EMPNO) (1/LN))'))
%HEX (hexadecimal-argument)

%HEX accepts an argument and returns the hexadecimal equivalent of the argument's value. The hexadecimal argument can be of any type. The returned value is of type *CHAR, and is tagged with the CCSID of the current job.

%HOUR (date/time-argument)
%HOUR accepts a date/time argument and returns the hour part of the value. The date/time argument can be a time or timestamp field, a time duration or timestamp duration (either field or literal), or a numeric field or literal. The returned value is of type *BIN4.
A numeric field argument must be defined as packed decimal (*DEC) with 6 digits and 0 precision for time duration or packed decimal (*DEC) with 20 digits and 6 precision for timestamp duration. A numeric constant argument must have 6 digits followed by a decimal point, or 14 digits followed by a decimal point and 6 digits. Example:
Example:   OPNQRYF      FILE(library/file)
     QRYSLT(('%HOUR(timefld2) = 12'))
%LEN (length-argument)
%LEN accepts one argument and returns the number of bytes used to represent the value unless the value is a graphic field type. If the value is a graphic field type, the number of graphic characters is returned. The length argument can be of any type. The returned value is of type *BIN4. Example:
OPNQRYF      FILE(library/file)
     QRYSLT('%LEN(varlenfld) <= 30')

Argument Type         Result Length in Bytes --------------------  ----------------------
Character             1-32766
Hex                   1-32766
DBCS-only             4-32766
DBCS-either           4-32766
DBCS-open             4-32766
DBCS-graphic          1-16383
Variable Character    0-32740
Variable Hex          0-32740
Variable DBCS-only    0-32740
Variable DBCS-either  0-32740
Variable DBCS-open    0-32740
Variable DBCS-graphic 0-16370
Date                  4
Time                  3
Timestamp             10
Binary *BIN4          2
Binary *BIN8          4
Floating point *FLT4  4
Floating point *FLT8  8
Packed decimal (p,s)  INTEGER(p/2)+1, (1-16)
Zoned decimal  (p,s)  p (1-31)
--------------------------------------------
p=precision, s=scale
String notes: The %LEN function returns the length of the value as it is stored in the data space.

For fixed-length fields, the length is always the same as the declared size of the field, not the length of the actual data in the field.

For variable-length fields, the length is the length of the actual data in the field, including trailing blanks.
For example, assume FIXED10 is a *CHAR(10) field, and VAR10 is a *VCHAR(10) field. The following example shows results of the %LEN function:
%LEN Statement  Field Data    Result --------------  ------------  ------
%LEN(fixed10)   '1234567890'  10
%LEN(fixed10)   '12345'       10
%LEN(var10)     '1234567890'  10
%LEN(var10)     '12345'        5
%LEN(var10)     '12345  '      7
%LEN(var10)     ''             0
%LN (numeric-argument)

%LN accepts a numeric argument and returns the natural logarithm of the argument. %LN and %EXP are inverse operations.
The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. Arguments of these types can be specified either as fields or literal values. The returned value is a double-precision floating-point number (*FLT8).

%LOG (numeric-argument)

%LOG accepts a numeric argument and returns the common logarithm (base 10) of the argument. %LOG and %ANTILOG are inverse operations.
The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. Arguments of these types can be specified either as fields or literal values. The returned value is a double-precision floating-point number (*FLT8).

%MAX (numeric-or-string-or-date/time-argument ...)

%MAX accepts one or more character-string, DBCS-string, numeric, or date/time arguments, and returns the largest value from the list. Date/time arguments are arguments of type *DATE, *TIME, or *TIMESTP, or arguments that are date, time, or timestamp durations. String arguments must be no longer than 256 bytes.
If only one argument is specified, this function returns the maximum value of its argument for the group of records defined on the GRPFLD parameter, and the returned value has the same attributes as the argument. If no records are selected, the result is the null value. If the single argument is a date duration, time duration, or timestamp duration, then the returned value is a packed decimal number (*DEC) with 8 digits and 0 precision (date duration), 6 digits and 0 precision (time duration), or 20 digits and 6 precision (timestamp duration). When a single argument is used, it must be a field name or an expression (not a literal). %MAX with only one argument is an aggregate function that is used for a nongrouping field in a query that uses the grouping function.
If multiple arguments are specified, %MAX returns the maximum value of all the arguments. All arguments must be either character-string, DBCS-string, numeric, or date/time values. This function calculates the maximum value of the first two arguments, and then continues to determine the maximum value of the previous result and the next successive argument. The final result is determined according to the following value conversion rules.
If an argument has different attributes than the previous result, the two values are converted to identical attributes and the operation continues. This conversion uses packed decimal if both values are fixed-point numeric values, or floating-point if either value is floating-point. The conversion for fixed-point numeric values aligns the decimal points and pads the values with zeros. Numeric type changes might truncate fractional digits if more than 31 total digits are required for fixed-point numbers, or drop some of the least significant digits if more than 15 total digits are required for floating-point numbers. Character values are changed by padding the shorter field with blanks.

%MICSEC (date/time-argument)

%MICSEC accepts a date/time argument and returns the microsecond part of the value. The date/time argument can be a timestamp (field or literal), a timestamp duration (field or literal), a character field that contains the external form of a timestamp, or a numeric field or literal. The returned value is of type *BIN4. A numeric field argument must be defined as packed decimal (*DEC) with 20 digits and 6 precision for timestamp duration. A numeric constant argument must be 14 digits followed by a decimal point and 6 digits.

%MIN (numeric-or-string-or-date/time-argument ...)

%MIN accepts one or more character-string, DBCS-string, numeric, or date/time arguments, and returns the smallest value from the list. Date/time arguments are arguments of type *DATE, *TIME, or *TIMESTP, or arguments that are date, time, or timestamp durations. String arguments must be no longer than 256 bytes.
If only one argument is specified, this function returns the minimum value of its argument for the group of records defined on the GRPFLD parameter, and the returned value has the same attributes as the argument. If no records are selected, the result is the null value. If the single argument is a date duration, time duration, or timestamp duration, then the returned value is a packed decimal number (*DEC) with 8 digits and 0 precision (date duration), 6 digits and 0 precision (time duration), or 20 digits and 6 precision (timestamp duration). When a single argument is used, it must be a field name or an expression (not a literal). %MIN with only one argument is an aggregate function that is used for a nongrouping field in a query that uses the grouping function.
If multiple arguments are specified, %MIN returns the minimum value of all the arguments. All arguments must be either character-string, DBCS-string, numeric, or date/time values. This function calculates the minimum value of the first two arguments, and then continues to determine the minimum value of the previous result and the next successive argument. The final result is determined by the value change rules described below.
If an argument has different attributes than the previous one, the two values are changed to identical attributes and the operation continues. This change uses packed decimal numbers if both values are fixed-point numeric values, or floating-point numbers if either value is a floating-point number. The change for fixed-point numeric values aligns the decimal points and pads with zeros. Numeric type change might truncate fractional digits if more than 31 total digits are required for fixed-point numbers, or might drop some of the least significant digits if more than 15 total digits are required for floating-point numbers. Character values are changed by padding the shorter field with blanks.

%MINUTE (date/time-argument)

%MINUTE accepts a date/time argument and returns the minute part of the value. The date/time argument can be a time or timestamp field, a time duration or timestamp duration (either field or literal), or a numeric field or literal. The returned value is of type *BIN4.
A numeric field argument must be defined as packed decimal (*DEC) with 6 digits and 0 precision for time duration or packed decimal (*DEC) with 20 digits and 6 precision for timestamp duration. A numeric constant argument must have 6 digits followed by a decimal point, or 14 digits followed by a decimal point and 6 digits.

%MONTH (date/time-argument)

%MONTH accepts a date/time argument and returns the month part of the value. The date/time argument can be a date or timestamp field, a date duration or timestamp duration (field or literal), or a numeric field or literal. The returned value is of type *BIN4.
A numeric field argument must be defined as packed decimal (*DEC) with 8 digits and 0 precision for date duration or packed decimal (*DEC) with 20 digits and 6 precision for timestamp duration. A numeric constant argument must have 8 digits followed by a decimal point, or 14 digits followed by a decimal point and 6 digits.

%NODENAME (integer-argument)
%NODENAME accepts an integer-argument which is used to identify a file that has been specified on the FILE parameter. The argument must be greater than 0 and less than or equal to the number of files specified on the file parameter. The %NODENAME function returns the RDB name for the record retrieved. The returned value is of type *VCHAR of length 18.
Find the node name for every record of the EMPLOYEE table. Example:
OPNQRYF      FILE((CORPDATA/EMPLOYEE))
     FORMAT(FNAME)
     MAPFLD((NODENAME '%NODENAME(1)'))
Join the EMPLOYEE and DEPARTMENT tables, select the employee number (EMPNO) and determine the node from which each record involved in the join originated. Example:
OPNQRYF   FILE((CORPDATA/EMPLOYEE) (CORPDATA/DEPARTMENT))
  FORMAT(FNAME)
  JFLD((EMPLOYEE/DEPTNO DEPARTMENT/DEPTNO *EQ))
  MAPFLD((EMPNO 'EMPLOYEE/EMPNO')
         (NODENAME1 '%NODENAME(1)')
         (NODENAME1 '%NODENAME(2)'))
Join the EMPLOYEE and DEPARTMENT tables, select all records of the result where the records of the two tables are on the same node. Example:
OPNQRYF   FILE((CORPDATA/EMPLOYEE) (CORPDATA/DEPARTMENT))
  FORMAT(FNAME)
  JFLD((1/NODENAME1 2/NODENAME2 *EQ))
  MAPFLD((NODENAME1 '%NODENAME(1)')
         (NODENAME2 '%NODENAME(2)'))
%NODENUMBER (integer-argument)
%NODENUMBER accepts an integer-argument which is used to identify a file that has been specified on the FILE parameter. The argument must be greater than zero and less than or equal to the number of files specified on the file parameter. The %NODENUMBER function returns a 4-byte binary number (*BIN4) with 10 total decimal digits and no fraction digits. The returned value will be the node number of the record selected.
If the argument identifies a non-distributed file, the value zero is returned.
For OPNQRYF the node number from the secondary file where the outer or exception join is performed will be returned.
If CORPDATA.EMPLOYEE is a distributed file, then the node number for each record and the employee name will returned. Example:
OPNQRYF      FILE((CORPDATA/EMPLOYEE))
     FORMAT(FNAME)
     MAPFLD((NODENAME '%NODENUMBER(1)')
            (LNAME '1/LASTNAME'))
%NONNULL (argument ...)
%NONNULL accepts a list of two or more arguments and returns the first non-null value from the list. The items in the argument list can be fields or literal values of any type. The type of the returned value is that of the item selected from the list.Example:
OPNQRYF      FILE(library/file)
     QRYSLT('%NONNULL(fld1 fld2 0) > 0')
The above example selects records from the file where either field FLD1 or field FLD2 contains a non-null value that is greater than zero. If both FLD1 and FLD2 were null, the %NONNULL function specified in this example would return '0' because of the constant '0' passed as the third argument. If any field is DBCS-graphic, all fields must be DBCS-graphic.
%NOT (string-argument)

%NOT accepts a character or hexadecimal string argument and returns a string that is the bit-wise 'NOT' (logical not) of the argument. The returned value is a string of type *HEX with the same length as the argument.

%OR (string-argument ...)

%OR accepts two or more character-string arguments and returns a string that is the bit-wise 'OR' (logical inclusive or) of the arguments. This function takes the first argument string, ORs it with the next string, and then continues to OR each successive argument with the previous result. If an argument is encountered that is shorter than the previous result, it is padded with blanks. The final result is a string with the same length as the longest argument. If any of the arguments are variable-length, the maximum length is used as the length of the argument.

%PARTITION (integer-argument)
%PARTITION accepts an integer-argument which is used to identify a file that has been specified on the FILE parameter. The argument must be greater than 0 and less than or equal to the number of files specified on the file parameter. The partition function returns a 4-byte binary number (*BIN4) with 10 total decimal digits and no fraction digits. The returned value will be the partition number of the record.
If the argument identifies a non-distributed file then a value of zero will be returned.
Find the PARTITION number for every row of the EMPLOYEE table. This can be used to determine if there is data skew. Example:
OPNQRYF FILE((CORPDATA/EMPLOYEE))
     FORMAT(FNAME)
     MAPFLD((PART1 '%PARTITION(1)'))
Select the employee number (EMPNO) from the EMPLOYEE table for all records where the partition number is equal to 100. Example:
OPNQRYF      FILE((EMPLOYEE))
     QRYSLT('%PARTITION(1) *EQ 100')
Join the EMPLOYEE and DEPARTMENT tables, select all records of the result where the records of the two tables have the same partition number Example:
OPNQRYF      FILE((CORPDATA/EMPLOYEE) (CORPDATA/DEPARTMENT))
     FORMAT(FNAME)
     JFLD((1/PART1 2/PART2 *EQ))
     MAPFLD((PART1 '%PARTITION(1)')
     (PART2 '%PARTITION(2)'))
%SECOND (date/time-argument)

%SECOND accepts a date/time argument and returns the seconds part of the value. The date/time argument can be a time or timestamp field, a time duration or timestamp duration (either field or literal), or a numeric field or literal. The returned value is of type *BIN4.
A numeric field argument must be defined as packed decimal (*DEC) with 6 digits and 0 precision for time duration or packed decimal (*DEC) with 20 digits and 6 precision for timestamp duration. A numeric constant argument must have 6 digits followed by a decimal point, or 14 digits followed by a decimal point and 6 digits.

%SIN (numeric-argument)

%SIN accepts a numeric argument and returns the sine of the argument. The argument must be specified in radians. %SIN and %ASIN are inverse operations.
The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. Arguments of these types can be specified either as fields or literal values. The returned value is a double-precision floating-point number (*FLT8).

%SINH (numeric-argument)

%SINH accepts a numeric argument and returns the hyperbolic sine of the argument. The argument must be specified in radians.
The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. Arguments of these types can be specified either as fields or literal values. The returned value is a double-precision floating-point number (*FLT8).

%SQRT (numeric-argument)

%SQRT accepts a numeric argument and returns the square root of the argument.
The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. Arguments of these types can be specified either as fields or literal values. The returned value is a double-precision floating-point number (*FLT8).

%SST (string-argument start-position-expression <length-expression>)
%SST and %SUBSTRING accept a character, hexadecimal, DBCS, or graphic string, a starting position expression, and an optional length expression as arguments. They return a substring of the string argument that is of the same type and CCSID as the string argument and has length equal to the value specified by the length-expression.
Single-byte substringing is done when these functions (%SST and %SUBSTRING) are used for DBCS data. The shift-out and shift-in characters might be lost, which produces unusual results. The result of the DBCS substring operation is the DBCS-open type.
The string argument can be a fixed- or variable-length character, hexadecimal, DBCS, or graphic field or an expression which evaluates to a fixed- or variable-length character, hexadecimal, DBCS, or graphic string.
The values derived from expressions for the second and third arguments must be valid integers. The second argument must have a value between 1 and the length attribute (or maximum length of a variable-length field) of the first argument, and the third argument must have a value between 1 and the length attribute (or maximum length of a variable-length field) of the first argument.
If an argument is DBCS-graphic, the second and third arguments must also be specified as DBCS-graphic characters, not bytes.
If an expression is given for the second or third arguments, the expression must be enclosed in parentheses.
If the expressions evaluate to variable-length results, no validation of the range of these expressions is guaranteed and errors might occur during input/output processing.
The maximum value allowed for the third argument (length) is 32766 except for DBCS-graphic, which is 16383. However, if the third operand is represented by an expression, this causes the result to be variable-length. Thus, the value of the expression cannot exceed 32740 except for DBCS-graphic, which cannot exceed 16370. The user can omit the third argument. If the third argument is not specified and the first argument is:

fixed-length, the default value for the third argument is LENGTH(argument_1) - value_for_argument_2 + 1

variable-length, the default value for the third argument is the maximum of 0 and LENGTH(argument_1) - value_for_argument_2 + 1

variable-length with a length less than the value for argument_2, the default value for the third argument is zero and the result is the empty string.
Example:
OPNQRYF      FILE(library/file)
     QRYSLT('field1 =
     %SST(field2 (numfld1+3)
     (numfld1+numfld2))')
%STDDEV (numeric-argument)

%STDDEV accepts a numeric argument and returns the standard deviation of its argument for the group of records defined by the GRPFLD parameter. The argument must be a field name or an expression (not a literal). If no records are selected, the result is the null value. Otherwise, the returned value is a double-precision floating-point number (*FLT8). %STDDEV is an aggregate function that is used for a nongrouping field in a query that uses the grouping function.

%STRIP (string-argument <strip-character><strip-function>)
%STRIP accepts a character-, DBCS-, or graphic-string argument, an optional strip character, and an optional strip function as arguments. It returns a result string with the strip character removed from the string argument as specified by the strip function.
The string argument can be a literal, a fixed or variable-length character, hexadecimal, DBCS, or graphic field, or an expression which evaluates to a fixed- or variable-length character, hexadecimal, DBCS, or graphic string.
The strip character must be a single character, enclosed in apostrophes, with a data type compatible to the source string. The default is a single SBCS space for character data, DBCS-open, and DBCS-either, a single DBCS space for DBCS-only data, and a single graphic space for graphic data. The strip function can be one of three functions:

*LEAD

Remove leading strip character(s)

*TRAIL

Remove trailing strip character(s)

*BOTH

Remove both leading and trailing strip character(s)

The default strip function is *BOTH. Example:
OPNQRYF      FILE(library/file)
     QRYSLT('%STRIP(fld '.' *TRAIL) = 'Mr')
%SUBSTRING (string-field-name start-position length)

%SUBSTRING performs the same operation as %SST. See the %SST description for more information.

%SUM (numeric-argument)

%SUM accepts a numeric argument and returns the sum of all the values for its argument in the group of records defined on the GRPFLD parameter and must be enclosed in parentheses. The argument must be a field name or an expression (not a literal).The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. If no records are selected, the result is the null value. Otherwise,

If the argument is floating-point number, the returned value is a double-precision floating-point number (*FLT8).

If the argument is a binary number with zero-precision, the returned value is *BIN4.

If the argument is a binary number with nonzero precision or a fixed-point number, the returned value is a packed decimal number (*DEC) with 31 total digits and as many fractional digits as the argument.

If the argument is of type date duration, time duration, or timestamp duration, the returned value is a double-precision floating-point number (*FLT8).

%SUM is an aggregate function that is used for a nongrouping field in a query that uses the grouping function.

%TAN (numeric-argument)

%TAN accepts a numeric argument and returns the tangent of the argument. The argument must be specified in radians. %TAN and %ATAN are inverse operations.
The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. Arguments of these types can be specified either as fields or literal values. The return value is a double-precision floating-point number (*FLT8).

%TANH (numeric-argument)

%TAN accepts a numeric argument and returns the hyperbolic tangent of the argument. The argument must be specified in radians. %TANH and %ATANH are inverse operations.
The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. Arguments of these types can be specified either as fields or literal values. The returned value is a double-precision floating-point number (*FLT8).

%TIME (date/time-argument)

%TIME accepts a date/time argument and returns a time. The date/time argument can be a time or timestamp field, a character or hexadecimal field containing the external form of a time, or a time literal. The returned value is of type *TIME.

%TIMESTP (date/time-argument date/time-argument)

%TIMESTP accepts one or two date/time arguments and returns a timestamp.

If only one date/time argument is specified, it must be a timestamp (field or literal), or a character or hexadecimal field containing the external form of a timestamp.

If both arguments are specified,

The first date/time argument must be a date (field or literal), or a character or hexadecimal field containing the external form of a date.

The second date/time argument must be a time (field or literal), or a character or hexadecimal field containing the external form of a time.

The returned value is of type *TIMESTP.

%USER
%USER does not support any arguments. It returns the user profile name of the job in which the query is running. The returned value is of type variable-length character (*VCHAR) with a maximum length of 18. Example:
OPNQRYF      FILE(library/file)
     QRYSLT('field = %USER')
%VAR (numeric-argument)

%VAR accepts a numeric argument and returns the variance of its argument for the group of records defined by the GRPFLD parameter. The argument must be a field name or an expression (not a literal).
The following argument types are treated as numeric values: date duration, time duration, and timestamp duration. If no records are selected, the result is the null value. Otherwise, the returned value is a double-precision floating-point number (*FLT8). %VAR is an aggregate function that is used for a nongrouping field in a query that uses the grouping function.

%XLATE (string-argument qualified-table)

%XLATE accepts a character-string argument and the name of a table object (*TBL), and returns a string that is the value of the first argument translated by using the contents of the table. The returned value is a string with the same length and CCSID as the first argument.
The second argument must be a simple or qualified table object name. If no library name is specified, *LIBL is used to find the table.

%XOR (string-argument...)

%XOR accepts two or more character-string arguments and returns a string that is the bit-wise 'XOR' (logical exclusive or) of the arguments. This function takes the first argument string, XORs it with the next string, and then continues to XOR each successive argument with the previous result. If an argument is encountered that is longer than the previous result, the previous result is padded with blanks before the XOR operation. If any of the arguments is variable-length, the maximum length is used as the length of the argument. The final result is a string of type *HEX with the same length as the longest argument.

%YEAR

%YEAR accepts a date/time argument and returns the year part of the value. The date/time argument can be a date or timestamp field, a date duration or timestamp duration (field or literal), or a numeric field or literal. The returned value is of type *BIN4.
A numeric field argument must be defined as packed decimal (*DEC) with 8 digits and 0 precision for date duration or packed decimal (*DEC) with 20 digits and 6 precision for timestamp duration. A numeric constant argument must have 8 digits followed by a decimal point, or 14 digits followed by a decimal point and 6 digits.
Parent topic:
Using Open Query File (OPNQRYF) command