Specifying Field Conditions

A field condition is a statement about a field in a logical record that evaluates as true or false. It is used in the WHEN, NULLIF, and DEFAULTIF clauses.

Note:

If a field used in a clause evaluation has a NULL value, then that clause will always evaluate to FALSE. This feature is illustrated in Example 10-5.

A field condition is similar to the condition in the CONTINUEIF clause, with two important differences. First, positions in the field condition refer to the logical record, not to the physical record. Second, you can specify either a position in the logical record or the name of a field in the data file (including filler fields).

Note:

A field condition cannot be based on fields in a secondary data file (SDF).

The syntax for the field_condition clause is as follows:

Description of the illustration fld_cond.eps

The syntax for the pos_spec clause is as follows:

Description of the illustration pos_spec.eps

Table 10-4 describes the parameters used for the field condition clause. For a full description of the position specification parameters, see Table 10-1.

Table 10-4 Parameters for the Field Condition Clause

Parameter	Description
`pos_spec`	Specifies the starting and ending position of the comparison field in the logical record. It must be surrounded by parentheses. Either `start`-`end` or `start`:`end` is acceptable. The starting location can be specified as a column number, or as `` (next column), or as `+n` (next column plus an offset). If you omit an ending position, then the length of the field is determined by the length of the comparison string. If the lengths are different, then the shorter field is padded. Character strings are padded with blanks, hexadecimal strings with zeros.
`start`	Specifies the starting position of the comparison field in the logical record.
`end`	Specifies the ending position of the comparison field in the logical record.
`full_fieldname`	`full_fieldname` is the full name of a field specified using dot notation. If the field `col2` is an attribute of a column object `col1`, then when referring to `col2` in one of the directives, you must use the notation `col1`.`col2`. The column name and the field name referencing or naming the same entity can be different, because the column name never includes the full name of the entity (no dot notation).
`operator`	A comparison operator for either equal or not equal.
`char_string`	A string of characters enclosed in single or double quotation marks that is compared to the comparison field. If the comparison is true, then the current record is inserted into the table.
`X'hex_string`'	A string of hexadecimal digits, where each pair of digits corresponds to one byte in the field. It is enclosed in single or double quotation marks. If the comparison is true, then the current record is inserted into the table.
`BLANKS`	Enables you to test a field to see if it consists entirely of blanks. `BLANKS` is required when you are loading delimited data and you cannot predict the length of the field, or when you use a multibyte character set that has multiple blanks.