Environment Catalog

Column Statement

COLUMN column_name
[INTERNAL]
DATATYPE datatype_spec
LENGTH n
[SCALE n]
[PRECISION n]
[FORMAT format_spec]
[PHYSICAL "native_column_name"]
[USAGE usage_spec]
[CENTURY THRESHOLD n]
[OMNIDEX
[DATATYPE datatype_spec]
[LENGTH n]
[FORMAT format_spec]
]
[AS select_item]
[SQL SELECT "string"]
[PROMPT "string"]
[HEADING "string"]
[LABEL "string"]
[CARDINALITY n]

COLUMN column_name

[[[environment_name.]database_name.]table_name.]column_name[(start, length)]

column_name represents a non-ambiguous column or portion of a column.

column_name MUST BEGIN WITH A LETTER, A-Z (case insensitive) and may contain up to 32 characters including letters, numbers and allowed special characters.

If column_name contains any reserved words or allowed special characters, it must be enclosed in single or double quotes.

start is the starting byte within column relative to one.

length is the number of characters after the starting byte to include.

INTERNAL

The INTERNAL section is Required, however the keyword INTERNAL is optional. It is a section identifier for the INTERNAL section of the COLUMN definition. INTERNAL refers to the actual data as it is stored in the DBMS.

Use of the keyword when also using an Omnidex section improves the readability of the Environment Catalog source code.

DATATYPE datatype_spec

Required. Declares the data type format of the defined column. The DATATYPE keyword is required. See Supported Datatypes for a list of supported datatypes.

LENGTH n

Required. Declares the storage length (as n) of the column in bytes.

C String data is terminated by a null character. Therefore, length must be increased by 1 to accommodate the null character. For example, a two character State field defined as a C String will have a length of 3: two for the abbreviated state and one for the null character.

SCALE n

Optional. Sets the magnitude of numeric values. OmniAccess does not format data based on SCALE. SCALE is reported in the Environment file and the System Catalog. SCALE is used by the Omnidex ODBC and JDBC drivers as well as for meta-data purposes.

PRECISION n

Optional. Specifies the number of decimal places in numeric values. OmniAccess does not format data based on PRECISION. PRECISION is reported in the Environment file and the System Catalog. PRECISION is used by the Omnidex ODBC and JDBC drivers as well as for meta-data purposes.

FORMAT format_spec

Optional. Applies to Omnidex date class data types only. This means this setting will only apply to flat file databases, since a flat file database is the only database that will contain an Omnidex date class data type. Refers to the granularity of the date data stored in the database.

This setting DOES NOT affect the appearance of returned data.

PHYSICAL "native_column_name”

Optional. This is a quoted string of up to 255 characters that maps the logical column name to the native column's name. This refers to native columns. Expressions and functions are not allowed.

USAGE usage_spec

USAGE [ASCENDING] [AUTO] ROWID

Optional. Defines a column as the id column. Use AUTO for columns where the id value is automatically assigned by the DBMS. Use ASCENDING only when the id value is 100% guaranteed to be sequentially ascending from 1.

CENTURY THRESHOLD n

Optional. Specifies the year cutoff for determining the century in 2 digit years. For example, if the century threshold is set to 60, a 2 digit year of 45 would be translated to 2045 and a 2 digit year of 61 would be translated to 1961. This setting over-rides the century threshold setting in the environment section.

OMNIDEX

Optional. The OMNIDEX section is used to index data in a format other than its native format. The keyword OMNIDEX is a section identifier for the OMNIDEX section of the COLUMN statement and is required if an OMNIDEX section is declared. Definitions in this section apply to the Omnidex indexes only.

This section is used ONLY if you want to override the way the indexes are stored by default. Most applications will not have an OMNIDEX section.

DATATYPE datatype_spec

Optional. Declares the data type format of the defined column. The DATATYPE keyword is required. See Supported Datatypes for a list of supported datatypes.

LENGTH n

Required. Declares the storage length (as n) of the column in bytes.

C String data is terminated by a null character. Therefore, length must be increased by 1 to accommodate the null character. For example, a two character State field defined as a C String will have a length of 3: two for the abbreviated state and one for the null character.

FORMAT format_spec

Optional. Applies to date class data types only. Refers to the granularity of the date data stored in the index files. NOTE: This setting DOES NOT affect the appearance of returned data.

AS "select_item"

Optional. Generates Pseudo-columns using SQL standard expressions and functions, with limitations. Pseudo-columns allows columns to be included in the environment file that do not exist in the native database.

select_item can be an expression or function. The pseudo-column is read/write.

SQL SELECT "string"

Optional. Generates Pseudo-columns using native database functions and expressions. Values are read-only.

Unless the column data can only be generated through native database functions that are not supported by Omnidex, DISC highly recommends that the AS select_item (above) clause be used instead of the SQL SELECT string clause.

SQL SELECT clauses support any of the following tokens that represent the current DATABASE, TABLE or COLUMN.

• %d represents the DATABASE wherever it appears in the string.
• %t represents the TABLE wherever it appears in the string.
• %c represents the COLUMN wherever it appears in the string.

To pass double quotes ( " ) in a rule to the native database, enclose the string in single quotes ( ' ). The converse is true for single quotes. Mixing double quotes and single quotes within strings is not supported. The following illustrates a few valid examples:

SQL SELECT "to_char(%t.%c)"

SQL SELECT "%t.%c + %t.AMOUNT"

SQL SELECT "%t.%c * 1.0725"

PROMPT "string"

Optional. This is a string of up to 32 characters used when setting a prompt for a value for the field. Prompt is for meta-data purposes only. It is not used by Omnidex in any way but is included as meta-data with some info calls.

HEADING "string"

Optional. This is a string of up to 32 characters used when setting a heading or name for a field appearing above the column being reported. Heading is for meta-data purposes only. It is not used by Omnidex in any way but is included as meta-data with some info calls.

LABEL "string"

Optional. This is a string of up to 32 characters used when setting a label or name for a field, appearing to the left of the column being reported. Label is for meta-data purposes only. It is not used by Omnidex in any way but is included as meta-data with some info calls.

CARDINALITY n

The cardinality of a column is the number of distinct values in the column. This number should be near the actual column cardinality. Omnidex uses this number to better optimize some queries.

Top