SQL Programming Manual for TAL

NonStop SQL Version Issues
HP NonStop SQL Programming Manual for TAL—527887-001
D-6
Migrating a C10 Program to Run on a C30 System
Check your programs for these words in SQL statements and change any that are
present as correlation names, cursor names, SQL statement names, and column
names. The TAL compiler detects the use of these reserved words and issues a
syntax error.
•
Result of subqueries
If a subquery in a WHERE clause returns no rows, NULL is returned to the
subquery. To be ANSI compatible, this result has changed from error 100 (no rows
selected or modified) to NULL for the NonStop SQL C10 implementation.
For example, consider this SELECT statement from tables TABLE1 and TABLE2,
with the SELECT from TABLE2 being a subquery that returns no rows:
SELECT * FROM table1 WHERE a = (SELECT a FROM table2)
Because the subquery returns no rows, the WHERE clause is evaluated as equal
to NULL and fails. The evaluation is: WHERE a = NULL.
To migrate programs affected by the change in the result of subqueries, you do not
have to code extra checking in the program. Be aware, however, that C30
programs might retrieve more rows than C10 programs, because the C10
programs would have received error 100 (no rows selected or modified).
•
Querying the NULLP value in the SQLDA structure
NULLP means null pointer. For C10, the system returns a system-defined value for
NULLP in the VAR^PTR field of the SQLDA structure if the names buffer is not
large enough.
A C30 system returns a negative value in the VAR^PTR field instead of NULLP.
Programs should check for a value less than zero instead of checking for NULLP.
NULLP is an internal constant and can change; therefore, its actual value is not
documented.
•
Additional SQLDA fields
Using a C30 SQLDA structure is necessary only if the program might encounter null
values or the new C30 data types FLOAT, REAL, DOUBLE PRECISION, DATETIME,
DATE, TIME, TIMESTAMP, and INTERVAL. The SQLDA structure for C30 has
additional fields that can contain information about these new C30 features. You can
use a release C10 SQLDA on a C30 system if the program does not encounter these
C30 features.
Caution. A table or view created with NonStop SQL C10 and having INNER, JOIN, or LEFT
as a column name or constraint name will not be available to an SQL program that references
these columns or constraints and is SQL compiled with the C30 SQL compiler. The SQL
compiler detects references to C30 reserved words and issues an error.
Note. A program can sometimes encounter null values even if there are no null values in the
database.