SQL Programming Manual for TAL

NonStop SQL Version Issues
HP NonStop SQL Programming Manual for TAL—527887-001
D-5
Migrating a C10 Program to Run on a C30 System
default is DEFAULT NULL. For example, these 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 NULL allowed
col2 CHAR(10) DEFAULT "XZ"
Under C10: col2 CHAR(10) DEFAULT "XZ" NOT NULL
Under C30: col2 CHAR(10) DEFAULT "XZ" NULL allowed
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
•
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 SQLCI OBEY command files
that create the tables that a migrated program uses. If you do not make these changes:
•
NonStop SQL creates the tables as Version 2 tables, which are not accessible
from a C10 system.
•
If null data is entered into the table and a program tries to retrieve a null value
without using an indicator variable, NonStop SQL returns error 8423.
•
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
release C10.
To migrate programs that use the AVG, MAX, MIN, or SUM aggregate functions,
recode the programs to determine if a null value is returned. Consider using use
one of these methods:
•
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 release C30 are INNER, JOIN, and LEFT.
Check for the use of these words as column names in tables or views or as
constraint names. Re-create any tables or views that use these names as column
names or constraint names.