Data Types for HP Neoview

Overview

Every column in a table has a name and a data type. The data type tells HP Neoview how much physical storage to set aside for the column and the form in which the data is stored. This section includes information about HP Neoview data types, null and default values, and data conversions.
For more information about HP Neoview data types and to determine which data types are available for your version of HP Neoview, see your HP Neoview SQL Reference Manual.
SAS/ACCESS Interface to HP Neoview does not directly support HP Neoview INTERVAL types. Any columns using these types are read into SAS as character strings.

String Data

CHAR(n)
specifies a fixed-length column for character string data. The maximum length is 32,708 characters.
VARCHAR(n)
specifies a varying-length column for character string data. The maximum length is 32,708 characters.

Numeric Data

LARGEINT
specifies a big integer. Values in a column of this type can range from –9223372036854775808 to +9223372036854775807.
SMALLINT
specifies a small integer. Values in a column of this type can range from –32768 through +32767.
INTEGER
specifies a large integer. Values in a column of this type can range from –2147483648 through +2147483647.
DOUBLE
specifies a floating-point number that is 64 bits long. Values in a column of this type can range from –1.79769E+308 to –2.225E-307 or +2.225E-307 to +1.79769E+308, or they can be 0. This data type is stored the same way that SAS stores its numeric data type. Therefore, numeric columns of this type require the least processing when SAS accesses them.
FLOAT
specifies an approximate numeric column. The column stores floating-point numbers and designates from 1 through 52 bits of precision. Values in a column of this type can range from +/–2.2250738585072014e-308 to +/–1.7976931348623157e+308 stored in 8 bytes.
REAL
specifies a floating-point number that is 32 bits long. Values in a column of this type can range from approximately –3.4E38 to –1.17E-38 and +1.17E-38 to +3.4E38.
DECIMAL | DEC | NUMERIC
specifies a fixed-point decimal number. The precision and scale of the number determines the position of the decimal point. The numbers to the right of the decimal point are the scale, and the scale cannot be negative or greater than the precision. The maximum precision is 38 digits.

Date, Time, and Timestamp Data

SQL date and time data types are collectively called datetime values. The SQL data types for dates, times, and timestamps are listed here. Be aware that columns of these data types can contain data values that are out of range for SAS.
DATE
specifies date values. The range is 01-01-0001 to 12-31-9999. The default format YYYY-MM-DD (for example, 1961–06–13). HP Neoview supports many other formats for entering date data. For more information, see your HP Neoview SQL Reference Manual.
TIME
specifies time values in hours, minutes, and seconds to six decimal positions: hh:mm:ss[.nnnnnn]. The range is 00:00:00.000000 to 23:59:59.999999. However, because the HP Neoview engine uses the ODBC-style interface to communicate with the HP Neoview server, fractional seconds are lost in the transfer of data from server to client.
TIMESTAMP
combines a date and time in the default format of yyyy-mm-dd hh:mm:ss[.nnnnnn]. For example, a timestamp for precisely 2:25 p.m. on January 25, 1991, would be 1991-01-25-14.25.00.000000. Values in a column of this type have the same ranges as described for DATE and TIME.

HP Neoview Null Values

HP Neoview has a special value called NULL. An HP Neoview NULL value means an absence of information and is analogous to a SAS missing value. When SAS/ACCESS reads an HP Neoview NULL value, it interprets it as a SAS missing value.
You can define a column in an HP Neoview table so that it requires data. To do this in SQL, specify a column as NOT NULL. This tells SQL to allow a row to be added to a table only if a value exists for the field. For example, NOT NULL assigned to the CUSTOMER field in the SASDEMO.CUSTOMER table does not allow a row to be added unless there is a value for CUSTOMER. When creating an HP Neoview table with SAS/ACCESS, you can use the DBNULL= data set option to indicate whether NULL is a valid value for specified columns.
For more information about how SAS handles NULL values, see Potential Result Set Differences When Processing Null Data .
To control how the DBMS handles SAS missing character values, use the NULLCHAR= and NULLCHARVAL= data set options.

LIBNAME Statement Data Conversions

This table shows the default formats that SAS/ACCESS Interface to HP Neoview assigns to SAS variables when using the LIBNAME statement to read from an HP Neoview table. These default formats are based on HP Neoview column attributes.
LIBNAME Statement: Default SAS Formats for HP Neoview Data Types
HP Neoview Data Type
SAS Data Type
Default SAS Format
CHAR(n)
character
$n.
VARCHAR(n)
character
$n.
LONGVARCHAR(n)
character
$n.
DECIMAL(p,s)
numeric
m.n
NUMERIC(p,s)
numeric
p,s
SMALLINT
numeric
6.
INTEGER
numeric
11.
REAL
numeric
none
FLOAT(p)
numeric
p
DOUBLE
numeric
none
LARGEINT
numeric
20.
DATE
numeric
DATE9.
TIME
numeric
TIME8.
TIMESTAMP
numeric
DATETIME25.6
The following table shows the default HP Neoview data types that SAS/ACCESS assigns to SAS variable formats during output operations when you use the LIBNAME statement.
LIBNAME Statement: Default HP Neoview Data Types for SAS Variable Formats
SAS Variable Format
HP Neoview Data Type
m.n
DECIMAL (m,n)1
other numerics
DOUBLE
$n.
VARCHAR(n)1
datetime formats
TIMESTAMP
date formats
DATE
time formats
TIME
1n in HP Neoview data types is equivalent to w in SAS formats.