SQL Programming Manual for Pascal

NonStop SQL Version Issues
HP NonStop SQL Programming Manual for Pascal—528614-001
D-5
Migrating an Application to Run on Release 2 (C30)
default is DEFAULT NULL. For example, the following column definitions result in
these default definitions for the indicated release:
col1 CHAR(10)
Under C10: col1 CHAR(10) DEFAULT SYSTEM NOT NULL
Under C30: col1 CHAR(10) DEFAULT NULL
col2 CHAR(10) DEFAULT "XZ"
Under C10: col2 CHAR(10) DEFAULT "XZ" NOT NULL
Under C30: col2 CHAR(10) DEFAULT "XZ"
In this example, null values are allowed in both of the C30 default column
definitions.
To ensure that tables created with C10 software retain their current definition if re-
created with C30 software, add the NOT NULL clause to all CREATE TABLE
column definitions if the clause is not already included. Also, add the DEFAULT
SYSTEM clause to all CREATE TABLE column definitions if a DEFAULT clause is
not already included. Make these changes to programs that create tables or to
NonStop SQL Conversational Interface (SQLCI) OBEY command file scripts that
create the tables a migrated program uses. If these changes are not made:
•
The table will be created as a Version 2 table and will not be accessable from a
C10 system.
•
Null data can be entered into the table as the default, which can result in
queries failing with error 8423 if the program attempts to retrieve a null value
without using an indicator variable.
•
Result of aggregate functions
The result returned in a SELECT statement of the aggregate functions AVG, MAX,
MIN, or SUM operating on an empty set is NULL. To be ANSI compatible, this
result has changed from error 100 (no rows selected or modified) to NULL for the
NonStop SQL C10 implementation.
To migrate programs that use the AVG, MAX, MIN, or SUM aggregate functions,
recode the programs to determine if a null value is returned. The program could
use one of the following two methods, with the first method being highly
recommended:
°
Check an indicator variable to handle a returned null value.
°
Check for error 8423 as well as error 100 for the result of the aggregate
function. Error 8423 indicates that the query is attempting to return a null value,
but there is no indicator variable along with the host variable to receive the null
value.
•
New SQL reserved words for C30 are INNER, JOIN, and LEFT
Check for the use of these words as column names in tables or views or as
constraint names. Recreate any tables or views that use these names as column
names or constraint names.