SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
N-9
Specifying Null Values in Host Programs
example uses the LIKE predicate to avoid entering the whole, exact table name for
the OD2 table. The column of interest is the DELIV_DATE column in table OD2.
>> SELECT NULLALLOWED FROM COLUMNS
+> WHERE TABLENAME LIKE "%OD2%" AND
+> COLNAME = "DELIV_DATE";
NULLALLOWED
-----------
Y
--- 1 row(s) selected.

This example queries the COLUMNS catalog table to display the value for the
NULLALLOWED column for all the columns of a particular table:
>> SELECT TABLENAME, COLNAME, NULLALLOWED
+> FROM COLUMNS
+> WHERE TABLENAME LIKE "%OD2%";

This example invokes a table description in SQL format (the default format through
SQLCI). The display shows NOT NULL for columns whose definition prohibits null
values.
>> INVOKE OD2;
-- Definition of table \SYS1.$VOL1.SALES.OD2
-- Definition current at 16:36:57 - 05/23/89
(
ORDERITEM DECIMAL( 6, 0 ) UNSIGNED NO DEFAULT
NOT NULL
, ORDERNUM NUMERIC( 6, 0 ) UNSIGNED NO DEFAULT
NOT NULL
, ORDER_DATE NUMERIC( 6, 0 ) NO DEFAULT
, DELIV_DATE NUMERIC( 6, 0 ) NO DEFAULT
, SALES_REP DECIMAL( 4, 0 ) UNSIGNED DEFAULT SYSTEM
, CUSTNUM DECIMAL( 4, 0 ) UNSIGNED NO DEFAULT
)
Specifying Null Values in Host Programs
Host programs use indicator variables to indicate the presence of null values. For more
information, see Indicator Variables and Indicator Parameters on page I-11 or the
SQL/MP programming manual for your host language.
DISTINCT, GROUP BY, and ORDER BY With Null Values
In evaluating the DISTINCT, GROUP BY, and ORDER BY clauses, SQL considers all
null values to be equal. Additional considerations for these clauses are:
DISTINCT Null values are considered duplicates; a result has at most
one null
GROUP BY The result has at most one null group
ORDER BY Null values are considered greater than nonnull values