Access to Oracle and Missing Values

[andreas_zaras]

Hello,

I connect to an Oracle datase through Access to Oracle. I write a proc sql on a single table and i ask sas to bring all the columns of the data souce and only the rows for which the valeus of the variable X is difrferent than 'GR', 'LP', 'GE'. Variable X contains missing values. The where clause that i use is:

where x not in ('GR','LP','GE')

Since there are missing values in X then rows with missing values in the variable X should appear. But what sas brings back is the rows for which the value of X is everything else then 'GR','LP','Ge', except missing values. Though if i write

where x is null

the missing values are brought back

ANy ideas how to resole this so that the missing vlaues apprear?

Regards,

ANdreas

[LinusH]

OR

[DBailey]

If you want the rows with missing values to be returned, then use this:

where coalesce(x,'XX') not in ('GR','LP','GE')

[LinusH]

Just curious, do you think the Oracle optimizer can handle these kind of logic (if x was either indexed or partitioned by)?

[andreas_zaras · Accepted solution]

Hello,

Thanks for your answer. I also found a useful link about my question:

http://ebookbrowse.com/chapman-tasha-and-lori-carlton-using-sas-with-oracle-pdf-d394357638

Null values In SAS, any selection criteria that includes “not equals” (^=, <>, or ne), “less than” (< or lt), or “less than or equal to” (<= or le) will include null or missing values. For example, the criteria Where Language ne “English” will pull records where the language is Spanish, French, Chinese, or blank. However, this is not the case in Oracle SQL. In Oracle, a null value is non-existent. It doesn‟t equal or not equal anything. For this reason, comparison operators will not apply for null records. Where Language ne “English” in an Oracle SQL statement will only pull records where the language is Spanish, French, or Chinese. Blank or null records will be excluded. To include null records in the selection criteria, this must be stated explicitly, i.e., Where Language ne “English” or Language is null

Regards,

Andreas