OLAP DML Data Types

Basic data types

OLAP DML data types fall into five categories which are referred to as basic data types and are described in the following table.

Basic Type	Specific Type
Numeric	INTEGER, SHORTINTEGER, DECIMAL, SHORTDECIMAL
Text	TEXT, ID
Boolean	BOOLEAN
Date	DATE
Time	DAY, WEEK, MONTH, QUARTER, YEAR

Different objects support the use of different data types for their values:

• For most data values, such as those stored in variables, the INTEGER, SHORTINTEGER, DECIMAL, SHORTDECIMAL, TEXT, ID, BOOLEAN, and DATE data types are supported.

• For dimension values, the INTEGER, TEXT, ID, DAY, WEEK, MONTH, QUARTER, and YEAR data types are supported.

Numeric data types

The following numeric data types are supported.

Data Type	Data Value
INTEGER	A whole number (in the range of ±2,147,483,647).
SHORTINTEGER	A whole number (in the range of ±32,767.
DECIMAL	A decimal number (with up to 15 significant digits).
SHORTDECIMAL	A decimal number (with up to 7 significant digits).

A value for any of these data types can begin with a plus (+) or minus (-) sign; it cannot contain commas. Additionally, a decimal value can contain a decimal point.

Examples of literal numeric values

Examples of literal numeric values are:

-1
256000
[phone]
10000000000.0009

Text data types

The following types of text data types are supported.

Data Type	Data Value
TEXT	Any number of alphanumeric characters enclosed in single quotes (').
ID	Up to 8 alphanumeric characters enclosed in single quotes (').

Escape sequences

In some cases, text data includes values that are not printable. Escape sequences are provided to allow such values to be input and displayed. An escape sequence is a series of alphanumeric characters that begins with a backslash.

The following table shows escape sequences that are recognized.

Escape Sequence	Meaning
\b	Backspace
\f	Form feed
\n	Linefeed
\r	Carriage return
\t	Horizontal tab
\"	Double quote
\'	Single quote
\\	Backslash
\dnnn	Character with ASCII code nnn decimal, where \d indicates a decimal escape and nnn is the decimal value for the character
\xnn	Character with ASCII code nn hexadecimal, where \x indicates a hexadecimal escape and nn is the hexadecimal value for the character

Examples of literal text values

Examples of literal text values are:

'First Quarter\'s Earnings'
'sales data eif'
'NONE'
'\n'
'JAN96'
'c:\\plan97\\budget.db'

BOOLEAN data type

A BOOLEAN data type is provided that you can use to represent logical values. In code, you can use any of the following values (in any combination of uppercase and lowercase characters) to represent Boolean values:

• YES, TRUE, ON

• NO, FALSE, OFF

By default, Boolean values as YES and NO are displayed. However, you can use the NOSPELL and YESSPELL functions to specify other values for display.

Working with Boolean expressions is discussed in "Boolean Expressions".

DATE data type

A DATE data type is provided that you can use to represent date values. Dates range from January 1, 1000, to December 31, 9999.

To control how values are formatted with the DATE data type in output, use the DATEFORMAT option. For more information on using the DATEFORMAT option, see the entry for the option in the OLAP DML Reference.

To represent DATE values, specify them in either quoted text or integer format.

Representing DATE values as quoted text

For quoted text, specify a group of characters enclosed in single quotes ('). Format the text in one of the styles specified by the DATEORDER option and described in the following table.

IF the style is...	THEN specify...	Examples
numeric,	the day, month, and year as three integer numbers with one or more separators between them.	'24/4/97' '24-04-1997'
packed numeric,	the day, month, and year as three integer numbers with no separators between them.	'240497' '04241997'
month name,	the day and year as integer numbers and the month as characters.	'24APR97' '24 ap 97' 'April 24, 1997'

See the entry for DATEORDER in the OLAP DML Reference for detailed rules for these styles.

Representing DATE values as integers

For an integer, specify a number between -328,717 and 2,958,464 (with the integer 1 corresponding to January 1, 1900).

Time data types

For dimensions, five time data types (DAY, WEEK, MONTH, QUARTER, and YEAR) are supported. You can specify a dimension value for a time dimension in either date or value name (VNF) format:

• To specify a value in date format, use any of the input styles listed for the DATEORDER option. You need to specify only the date components that are relevant for the data type that is defined for the dimension. If you specify a full date, the current value of the DATEORDER option is used to resolve any ambiguities.

• To specify a value in VNF format, use the formats outlined in the VNF command. A VNF is a template that controls the input and display format for values of a given time dimension. The template can include format specifications for any of the components that identify a time period (day, month, calendar year, fiscal year, and period within a fiscal year).

Note: You can use the MAKEDATE function to create a full date value from a day, month, and year. For more information on the MAKEDATE functions, see the entry for the function in the OLAP DML Reference.

Related information

For more information, see the following table.

IF you want documentation about . . .	THEN see . . .
functions that you can use to manipulate text, numbers, dates and time periods,	the OLAP DML Reference
specifying file names,	"Specifying File Names in the OLAP DML"