Field name

A database field name, defined field name, or a reserved field name

Constant

A data value that has an unchanging, self-defined value

For instance, 1 and ABC are constant values that are not altered during the execution of the statements.

Function

A special routine that is set up to convert, extract, or manipulate data values

Functions you can use are as follows:

BINARY(expression)

Converts the specified expression to internal binary format, resulting in a signed 4-byte binary number

CHAR(expression)

Converts the specified expression to character format

DATE(expression)

Converts the specified expression to internal date format.
If expression is a character field, the character string must be formatted in the default date format with a date separator character (typically a slash) between the month, day, and year. If expression is a binary or packed field, the field specifies a Julian date with the century indicator in form, CYYDDD.

EDIT(exp-1,exp-2)

exp-1 specifies the expression whose value is to be edited
exp-2 specifies the edit-pattern to be used for editing exp-1; exp-2 is a constant (for example, 'ZZZZ9')

The edit-pattern character for a character field is as follows:

X

Specifies substitution of a character from exp

Edit-pattern characters for a numeric field (binary or packed) are as follows:

$

Specifies the substitution of a currency symbol or a floating currency symbol

*

Specifies that an asterisk or a rolling asterisk is to be substituted in place of a leading zero

–

Specifies the substitution of a sign or floating sign for a negative value, blank for a positive value. + – specifies

the substitution of a sign or floating sign. + is substituted for a positive and - for a negative value

.

Specifies the substitution of a decimal point

,

Specifies the substitution of a comma

The substitution and suppression of this character can be affected by floating edit characters and zero suppression edit characters.

Edit-pattern characters for date fields are as follows:

X

Specifies the substitution of a numeric character

Z

Specifies the substitution of a numeric character; if the corresponding digit is a leading zero, blank is substituted

9

Specifies the substitution of a numeric character

D

Specifies the substitution of a one-digit day

DD

Specifies the substitution of a two-digit day

DDD

Specifies the substitution of a Julian day

M

Specifies the substitution of a one-digit month

MM

Specifies the substitution of a two-digit month

Y

Specifies the substitution of a one-digit year

YY

Specifies the substitution of a two-digit year

YYY

Specifies the substitution of a three-digit year

The three-digit year is the number of year from 1900. Year 2000 is represented as 100.

YYYY

Specifies the substitution of a full four-digit year

Z

Specifies the substitution of one character from data in the form MMDDYYYY YYYDDD (Gregorian and Julian)

9

Specifies the substitution of one digit from data in the form MMDDYYYY YYYDDD (Gregorian and Julian)

Function

Editing examples:

Sending Field

Edit-pattern

Resulting Field

'ABCDEF'

X-X-X

A-B-C

'A2534RW'

XXX/XXX/XXX

A25/34R/W

'BDF'

AXCXEX

ABCDEF

0

9999

0000

0

ZZZZ

123

ZZ,ZZ9

123

395

**,**9

***395

960

$$,$$9

$960

1005

ZZ,ZZ9

1,005

1256

-----9

1256

3471

+++++9

+3471

-523

--,--9

-523

-7000

++,++9

-7,000

-9275

ZZ,ZZ9

9,275-

24569

Z,ZZZ.99+

245.69+

192543

XX:XX:XX

19:25:43

999999

ZZZ9

****

36554 (date)

MM/DD/YYYY

01/30/2000

36583 (date)

YYYY.MM.DD

2000.02.28

36645 (date)

DD/MM/YY.DDD

30/04/00.121

36671 (date)

99/99/9999 999.999

05/26/2000 100.147

Function

PACK(expression)

Converts the specified expression to internal packed format

The resulting value is an 8-byte signed, packed number.

PREV(field-name)

Specifies the usage of the previous data value for field-name

SUBSTR(expression,pos,len)

Specifies the usage of a subset of the specified expression

The SUBSTR expression must result in or be a character value. pos specifies the beginning character to be extracted. len specifies the number of characters to be extracted.

TRANS(field,expression-value,expression-result
[expression-value, expression-result,…])

Specifies the matching of two values (expression and expression-value) and the use of a translated result (expression-result)

Expression-value and expression-result can be repeated for each required value and result. An asterisk can be specified as expression-value to indicate any value. If a matching value is not found after interrogation of all values, the value contained in the field is used as the translated result.

Operation

A group of field names, constants, or functions separated by operators:

field/constant/function op field/constant/function

The valid operators (op) are:

+ for adding fields
– for subtracting fields
* for multiplying fields
/ for dividing fields
|| for concatenating fields

The evaluation and computation of an operation is from left to right in the following order:

1. Concatenation
2. Multiplication and division
3. Addition and subtraction

The order of evaluation and computation can be altered by enclosing a portion or portions of an operation in parentheses. These parenthetic sections are evaluated first and follow the same order of evaluation as the total operation.