Unlike D3, SQL requires that each column has a predefined data type. For D3 SQL, the possible data types are CHAR, VARCHAR, INTEGER, SMALLINT, NUMERIC, DATE, TIME, and TIMESTAMP.
By default, SQL-CREATE-VIEW assigns data types to columns as described below:
The column representing the item-ID is assigned the data type VARCHAR.
For attribute-defining items containing an output conversion or correlative with a processing code beginning with mr, d, or mt. The following dictates the data type selected for the column descriptor:
If the mr0 or md0 code is detected, the data type of the column is INTEGER.
If the mr# or md# code (where # is some number other than 0) is detected, the data type is NUMERIC.
If the d# code (where # is some valid number) is detected, the data type is DATE.
If dd, dj, dm, dq, dw, dy or d#y (where # is a valid number), the data type is INTEGER.
For all other d processing codes, the data type is VARCHAR.
For all processing codes beginning with mt, the data type is TIME.
For all remaining attribute-defining items, the following dictates the data type selected for the column descriptor:
If no data is present in the file, all the column data types default to VARCHAR.
If data is present in the file, a small number of items are scanned and analyzed.
If the data is found to be numeric and does not contain decimal places, the column is assigned the data type INTEGER.
If the data does contain decimal places, the column is assigned the data type NUMERIC.
If any non-numeric data is found, the column is assigned the data type VARCHAR.
If the data typing assigned by SQL-CREATE-VIEW using the basic syntax does not accurately describe the data, then manual assignment of data types using the advanced syntax is required. In some instances, ODBC-compliant applications may not support a particular data type. For example, if the TIME data type is not supported by an application, then the attribute-defining item can be assigned a data type of VARCHAR using manual data typing See Manual data typing.