Constants that the BMC AMI Utilities SQL language supports

Integer constants

An integer constant specifies an integer as a signed number with a maximum of 19 digits and without a decimal point.

The data type of an integer constant is:

• A large integer if the value is within the range of a large integer
• A big integer if the value is outside the range of a large integer, but within the range of a big integer
• A long decfloat constant if the value is defined outside the range of big integer values

Floating-point constants

A floating-point constant specifies a double-precision floating-point number as two numbers separated by an E:

• The first number can include a sign and a decimal point.
• The second number can include a sign but not a decimal point.

The constant's value is the product of the first number and the power of 10 specified by the second number. If the value is outside the range of floating-point numbers, the product converts the value to a long decimal-floating point constant, as explained in Decimal floating-point constants.

Decimal constants

A decimal constant is a signed or unsigned number of no more than 31 digits that includes a decimal point.

The precision is the total number of digits, including those (if any) to the right of the decimal point. The total includes all leading and trailing zeros. The scale is the number of digits to the right of the decimal point, including trailing zeros.

Decimal floating-point constants

A decimal floating-point constant specifies a decimal floating-point number as two numbers separated by an E:

• The first number can include a sign and a decimal point.
• The second number can include a sign but not a decimal point.

The value of the constant is the product of the first number and the power of 10 specified by the second number. The value must be within the range of DECFLOAT(34).

Basically, a constant is a decimal floating-point constant only if one of the following conditions exists:

• The value is specified as two numbers separated by E, and the value is outside the range of a floating-point constant.
• The value is specified as a number that does not contain E, and the number has more than 31 digits.

In addition to numeric constants, you can use the following special values to specify decimal floating-point special values:

• INF or INFINITY— represents infinity
• NAN — represents quiet not-a-number
• SNAN — represents signaling not-a-number

You can precede the special values by an operational sign (+ or -).

When you use special values in a predicate, the following order of precedence applies:

-NAN < -SNAN < -INFINITY < -0 < 0 < INFINITY < SNAN < NAN

Character string constants

A character string constant specifies a fixed-length character string that does not exceed 255 characters.

The string is a sequence of characters enclosed by a pair of apostrophes ('). The string length does not include the delimiters. If the string itself contains a string delimiter, then it should be duplicated . Blanks within the string are preserved.

Hexadecimal constant

A hexadecimal constant is a string representation of hexadecimal digits in the form of X'HH…HH'.

The number of hexadecimal digits must not exceed 510. A hexadecimal digit can be a numeric digit, any letter from A through F, or a combination. Each pair of hexadecimal digits represents a character.

Graphic string constant

A graphic string constant specifies a fixed-length graphic string that does not exceed 128 characters. The string is in the following format:

G'<gg…..gg>'

gg is a graphic character (double byte) enclosed by a shift-in '<' and a shift out '>' characters.

DATE string constant

A DATE string constant contains a value that conforms to one of the valid formats for string representations of dates, subject to the following rules:

• Leading blanks are not allowed.
• You can omit leading zeros from the month and day elements of the date. An implicit specification of 0 is assumed for any omitted digit.
• You must include leading zeros for the year element of the date.
• You can include trailing blanks.

TIME string constant

A TIME string constant must contain a value that conforms to one of the valid formats for string representations of times, subject to the following rules:

• Leading blanks are not allowed.
• You can omit the seconds element of the time.
• You can include trailing blanks.
• If the USA format is not used and the minutes and seconds are all zeros, the hour can be 24. If the format is USA, mm may be omitted but hh must be specified.

TIMESTAMP string constant or TIMESTAMP WITH TIMEZONE constant

A string representation of a timestamp is a character string whose value conforms to a date string constant concatenated with a time string constant and separated by a hyphen (-).

A TIMESTAMP string constant must contain a value that conforms to a date string constant concatenated with a time string constant, and separated by a hyphen. It is subject to the following rules:

• The string is at least 16 characters.
• Imbedded blanks are not allowed.
• Trailing blanks are allowed.
• (optional) the constant can be concatenated with a maximum of 12 digits, representing fractions of a second, called precision.

Also, you can optionally append a time zone to the string according to the following syntax:

• +HH:MM
• -HH:MM

The variables are defined as follows:

• You can specify HH as a single H.
• You can specify MM as a single M. If you include MM or M, it must follow the colon; if you omit MM or M, omit the colon.

Binary string constant

A binary string constant specifies a varying-length binary string. It is specified using the designator BX followed by hex digits enclosed in apostrophes.

The number of hex digits must be even and the maximum length of the constant is 255 bytes.

Unicode string constant

A unicode string constant specifies a varying-length Double-Byte unicode string constant. It is specified using the designator UX followed by hex digits enclosed in apostrophes. The number of hex digits must be multiple of 4. Each 4 digits represent a Double-Byte character. The maximum length of the DBCS bytes is 254. The string can only be used when a Unicode table is in use.