Controls the format of the WHERE clause when you use the DBKEY= data set option.
| Valid in: | SAS/ACCESS LIBNAME statement |
|---|---|
| Category: | Data Set Control |
| Default: | YES [Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HAWQ, Impala, Microsoft SQL Server, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP IQ, Snowflake, Vertica, Yellowbrick] |
| NO [Informix] | |
| Data source: | Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HAWQ, Impala, Informix, Microsoft SQL Server, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP IQ, Snowflake, Vertica, Yellowbrick |
| Note: | Support for Yellowbrick was added in SAS 9.4M7. |
| See: | DBKEY= data set option, DBNULLKEYS= data set option |
Table of Contents
specifies that there might be NULL values in the key columns in a transaction table or a master table.
specifies that there are no NULL values in the key columns for a transaction table or a master table.
If there might be NULL values in the transaction table or the master table for the columns that you specify in the DBKEY= data set option, use DBNULLKEYS=YES. This is the default for most interfaces. When you specify DBNULLKEYS=YES and also a column that is not specified as NOT NULL in DBKEY=, SAS generates a WHERE clause that can find NULL values. For example, if you specify DBKEY=COLUMN and COLUMN is not specified as NOT NULL, SAS generates a WHERE clause with this syntax:
WHERE ((COLUMN = ?) OR ((COLUMN IS NULL) AND (? IS NULL)))With this syntax SAS can prepare the statement once and use it for any (NULL or NOT NULL) value in the column. This syntax can potentially be much less efficient than the shorter form of the WHERE clause below. When you specify DBNULLKEYS=NO or a column that DBKEY= specifies as NOT NULL, SAS generates a simple WHERE clause.
If you know that there are no NULL values in transaction or master tables for the columns that you specify in the DBKEY= option, you can use DBNULLKEYS=NO. This is the default for the Informix interface. If you specify DBNULLKEYS=NO and DBKEY=COLUMN, SAS generates a shorter form of the WHERE clause, regardless of whether the column that is specified in DBKEY= is specified as NOT NULL.
WHERE (COLUMN = ?)