Chapter Contents

Previous

Next

CA-OpenIngres SQL Data Types

Every column in a table has a name and a data type. The data type tells CA-OpenIngres how much physical storage to set aside for the column and the form in which the data is stored.

Character Data

The character data types for CA-OpenIngres SQL are listed below, where n is the length of the character string.

CHAR(n), Cn

is the fixed-length character string where n represents a string of 1 to 2000 ASCII characters. CHAR is preferred over C. CHAR can contain any printing or nonprinting characters and the NULL character. When comparing CHAR strings, blanks are significant.

For compatibility with previous versions of CA-OpenIngres, C is supported. C can contain only printing characters; nonprinting characters are converted to blanks. When comparing C strings, blanks are ignored.

VARCHAR(n) TEXT(n)

is the varying-length character string where n represents a string of 1 to 2000 ASCII characters. VARCHAR is preferred over TEXT. VARCHAR can contain any printing and nonprinting characters and the NULL character. When comparing VARCHAR strings, blanks are significant. VARCHAR, when comparing strings of unequal length, adds blanks to the end of the shorter string to make it the same length as the longer string.

TEXT can contain all ASCII characters except NULL characters, which are converted to blanks. When comparing TEXT strings, blanks are significant. TEXT, when comparing strings of unequal length, considers a shorter string "less than" a longer string if all characters up to the length of the shorter string are equal.

LONG VARCHAR

is the varying-length column for character string data. A LONG VARCHAR string can be up to two gigabytes in length. SAS/ACCESS does not currently allow you to use data in CA-OpenIngres LONG VARCHAR columns.

TEXT

is the varying-length character string that can contain all ASCII characters except NULL characters. NULL characters are converted to blanks. When comparing TEXT strings, blanks are significant. TEXT, when comparing strings of unequal length, considers a shorter string "less than" a longer string if all characters up to the length of the shorter string are equal. TEXT is supported for compatibility with previous versions of CA-OpenIngres.

Numeric Data

The numeric data types for CA-OpenIngres SQL are listed below.

REAL|FLOAT4, DOUBLE PRECISION|FLOAT|FLOAT8

Floating-point values consist of the following: an integer part, a decimal point, and a fraction part or scientific notation of the format that follows:

\[+[minus ]\]<digit >\[.[lt ]digit >\]eE\[+[minus ]\]<digit >

where the clauses that are enclosed in the angle brackets (<>) are optional and, if used, can be repeated. The FLOAT4 (4 byte) range is -1.0e+38 to +1.0e+38 with 7-digit precision. The FLOAT (8 byte) range is -1.0e+38 to +1.0e+38 with 16-digit precision.

The default character that indicates the decimal point is a period (.). This character can be changed by setting II_DECIMAL. For more information on setting II_DECIMAL, refer to your CA-OpenIngres installation and operations guide.

Note:    REAL is a synonym for FLOAT4. FLOAT8 and DOUBLE PRECISION are synonyms for FLOAT.  

SMALLINT|INTEGER2, INTEGER1, INTEGER|INTEGER4

Integers contain no fractional part. The three integer data types are INTEGER1 (1 byte), which has a range of -128 to +127, SMALLINT (2 byte), which has a range of -32,768 to +32,767, and INTEGER (4 byte), which has a range of -2,147,483,648 to +2,147,483,647.

Note:    INTEGER2 is a synonym for SMALLINT. INTEGER4 is a synonym for INTEGER.  

DECIMAL | DEC | NUMERIC

exact numeric data type defined in terms of its precision (total number of digits) and scale (number of digits to the right of the decimal point). The minimum precision for a decimal value is 1 and the maximum is 31. The scale of a decimal value cannot exceed its precision. Scale can be 0 (no digits to the right of the decimal point).

Abstract Data

CA-OpenIngres SQL date and money data types are abstract data types.

DATE

Dates are input as quoted character strings. CA-OpenIngres accepts U.S. date input formats, absolute time input formats, and combined date and time input formats. DATE (12 bytes) has a range of 1-JAN-1582 to 31-DEC-2382 (for absolute dates) and -800 years to 800 years (for time intervals).

CA-OpenIngres assumes that the year is the current year if the year is omitted. If a two-digit year is used in formats with delimiters such as forward slashes (/) or dashes (-), or if the year is omitted from the date, CA-OpenIngres assumes that the century is 1900.

CA-OpenIngres assumes the current (today's) date if the date is omitted from an absolute time entry.

Dates are stored in Greenwich Mean Time (GMT). The SAS/ACCESS interface to CA-OpenIngres takes care of converting dates. In the following tables, xxx represents a time zone designation. Valid time zone designations are edt, cdt, mdt, pdt, gmt, est, cst, mst, and pst. CA-OpenIngres assumes the local time zone if a time zone designation is not supplied.

At this time, the SAS/ACCESS interface to CA-OpenIngres does not support the CA-OpenIngres date and time interval formats.

The default date output format for CA-OpenIngres is U.S. You can change this format by setting II_DATE_FORMAT. For more information about II_DATE_FORMAT, refer to your CA-OpenIngres SQL reference manual and your CA-OpenIngres installation and operations guide.

For information about changing the date format conventions to accommodate international conventions, refer to your CA-OpenIngres SQL reference manual.

U.S. Input Formats

Format		Example
'mm/dd/yyyy'		'05/16/1991'
'dd-mmm-yyyy'		'16-may-1991'
'mm-dd-yyyy'		'05-16-1991'
'yyyy-mm-dd'		'1991-05-16'
'mmddyy'		'051691'
'mm/dd'		'05/16'
'mm-dd'		'05-16'
'today'		The string 'today' has today's date as its value.
'now'		The string 'now' has the current date and time as its value

Absolute Time Input Formats

Format		Example
'hh:mm:ss'		'10:30:00'
'hh:mm:ss xxx'		'10:30:00 est'
'hh:mm:ss [am|pm] xxx'		'10:30:00 am est'
'hh:mm'		'10:30'
'hh:mm xxx'		'10:30 est'

Combined Date and Time Input Formats

Format		Example
'mm/dd/yy hh:mm:ss'		'05/16/91 10:30:00'
'dd-mmm-yy hh:mm:ss'		'16-may-91 10:30:00'
'mm/dd/yy hh:mm:ss xxx'		'05/16/91 10:30:00 est'
'dd-mmm-yy hh:mm:ss xxx'		'16-may-91 10:30:00 est'
'dd-mmm-yy hh:mm:ss [am|pm] xxx'		'05-may-91 10:30:00 am est'
'mm/dd/yy hh:mm'		'01/16/91 10:30'
'dd-mmm-yy hh:mm'		'16-may-91 10:30'
'mm/dd/yy hh:mm xxx'		'05/16/91 10:30 est'
'dd-mmm-yy hh:mm xxx'		'05-may-91 10:30 est'

MONEY

Money values in CA-OpenIngres are stored as their actual money amount. Money values are significant to exactly two decimal places, and on input and output their amounts are rounded in dollars and cents. Arithmetic operations on MONEY retain the precision of two decimal places. MONEY (8 byte) has a range of $-999,999,999,999.99 to $999,999,999,999.99.

Money values in CA-OpenIngres are displayed as 20-character strings. The display format is $sdddddddddddd.dd where s is the sign (-for negative and no sign for positive), and d is a digit from 0 to 9.

CA-OpenIngres accepts money values on input as character strings, numbers, valid integers, or floating-point numbers.

For information about the logical names that affect the display of MONEY values, refer to your CA-OpenIngres installation and operations guide.

CA-OpenIngres NULL and Default Values

CA-OpenIngres has a special value that is called NULL. NULL means an absence of information and is analogous to the SAS System's missing value.

Columns can be defined as NOT NULL so that they cannot contain NULL data. NOT NULL tells CA-OpenIngres not to add a row to the table unless the row has a value for the column.

Columns can be defined so that they cannot contain NULL data. For example, the CREATE TABLE statement for the CUSTOMERS table in Appendix 1 defines the first column, CUSTOMER, as CHAR(8) and NOT NULL. NOT NULL tells CA-OpenIngres not to add a row to the table unless the row has a value for CUSTOMER.

LIBNAME Statement Data Conversions

The following table shows the default SAS System variable formats that the LIBNAME statement assigns to CA-OpenIngres data types during input operations.

LIBNAME Statement: Default SAS Formats for CA-OpenIngres Data Types

INGRES Data Type		Default SAS Format
CHAR(n) or Cn		$n. (n < 200) $200. (n > = 200)
VARCHAR(n) or TEXT(n		$n. (n < 200) $200. (n > = 200)
INTEGER1		4.0
SMALLINT (INTEGER2)		6.0
INTEGER (INTEGER4)		11.0
FLOAT4 (REAL)		none
FLOAT (FLOAT8)		none
DATE		DATETIME21.
MONEY		DOLLAR19.2
DATE INTERVAL		NOT SUPPORTED
TIME INTERVAL		NOT SUPPORTED
DECIMAL		m.n (m<= 31 and n<=m)

The following table shows the default CA-OpenIngres data types that the LIBNAME statement assigns to SAS variable formats during output operations.

LIBNAME Statement: Default CA-OpenIngres Data Types for SAS Variable Formats

SAS Variable Format		CA-OpenIngres Data Type
$w		CHAR(n)
any date format		DATE
w .0 (w<=2)		INTEGER1
w .0 (w>2 and w<=4)		SMALLINT
w .0 (where w>4 and w<9)		INTEGER
IBw.d, PIBw.d		INTEGER
all other numeric datatypes		FLOAT
DOLLAR		MONEY

ACCESS Procedure Data Conversions

The following table lists the default SAS System variable formats that the ACCESS procedure assigns to each CA-OpenIngres data type.

PROC ACCESS: Default SAS Formats for CA-OpenIngres Data Types

INGRES Data Type		SAS Variable Format
CHAR(n) or Cn		$n. (n < 200) $200. (n > = 200)
VARCHAR(n) or TEXT(n		$n. (n < 200) $200. (n > = 200)
INTEGER1		4.0
SMALLINT (INTEGER2)		6.0
INTEGER (INTEGER4)		11.0
FLOAT4 (REAL)		E14.0
FLOAT (FLOAT8)		E23.0
DATE		DATETIME21.
MONEY		DOLLAR19.2
DATE INTERVAL		NOT SUPPORTED
TIME INTERVAL		NOT SUPPORTED

DBLOAD Procedure Data Conversions

The following table lists the default CA-OpenIngres data type that the DBLOAD procedure assigns to each SAS variable format.

PROC DBLOAD: Default CA-OpenIngres Data Type for SAS Variable Formats

SAS Variable Format		INGRES Data Type
w.0		INTEGER1
w.0		SMALLINT
w.0		INTEGER
IBw.d, PIBw.d		INTEGER
all other numeric data types		FLOAT
DOLLAR		MONEY
any SAS date, datetime, or time format		DATE
	$n	CHAR(n)

Chapter Contents

Previous

Next

Top of Page

Copyright © 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.