Data Set Options for Relational Databases |
Controls
the format of the WHERE clause with regard to NULL values
when you use the DBKEY= data set option.
Default value: |
LIBNAME setting
|
Valid in: |
DATA and PROC steps (when accessing DBMS data
using SAS/ACCESS software)
|
DBMS support: |
Aster nCluster, DB2 under
UNIX and PC Hosts, DB2 under z/OS,
Greenplum, HP Neoview, Informix, Microsoft SQL Server, Netezza, ODBC, OLE
DB, Oracle, Sybase IQ
|
If there might be NULL values in the transaction
table or the master table for the columns that you specify in the DBKEY= option,
then use DBNULLKEYS=YES. When you specify DBNULLKEYS=YES and specify a column
that the DBKEY= data set option defines as NOT NULL, SAS generates a WHERE
clause to find NULL values. For example, if you specify DBKEY=COLUMN and COLUMN
is not defined as NOT NULL, SAS generates a WHERE clause with this syntax:
WHERE ((COLUMN = ?) OR ((COLUMN IS NULL) AND (? IS NULL)))
This syntax enables SAS to prepare the statement once and use it for
any value (NULL or NOT NULL) in the column. This syntax has the potential
to be much less efficient than the shorter form of the following WHERE clause.
When you specify DBNULLKEYS=NO or specify a column that is defined as NOT
NULL in the DBKEY= option, SAS generates a simple WHERE clause.
If you know that there are no NULL values in the transaction
table or the master table for the columns you specify in the DBKEY= option,
you can use DBNULLKEYS=NO. If you specify DBNULLKEYS=NO and specify DBKEY=COLUMN,
SAS generates a shorter form of the WHERE clause, regardless of whether the
column that is specified in DBKEY= is defined as NOT NULL:
WHERE (COLUMN = ?)
To assign this option to a group of relational
DBMS tables or views, see the
DBNULLKEYS= LIBNAME Option.
DBKEY= Data Set Option
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.