NULL_PROCESSOR Function

Facilitates the use of the Null_Info keyword for the DB_SQL function by extracting the list of rows containing missing for one or more columns.

Usage

table = NULL_PROCESSOR (null_info_object, ['col1', 'col2', ..., 'coln'], Comp=comp)

Input Parameters

null_info_object—The object returned by the Null_Info keyword in the DB_SQL call.

coli—The list of column names.

Keywords

Comp=compProduces the complement to the result, that is, the result contains a list of rows with missing data. comp contains a list of rows with no missing data.

Discussion

Assuming the following use of the ODBC_SQL Null_Info keyword:

table=odbc_sql(odbc_connect('oracle', 'user_id/user_pw'), 'select * from blanktest', null_info=foo)

where blanktest contains the data given in blanktest Data , which has missing data for ID_NO in the 4th, 9th, and 11th rows and missing data for ANIMAL_NAME in the 3rd, 8th, and 10th rows.

blanktest Data

ID_NO

ANIMAL_NAME

1

golden

2

chirpy

3

NULL

NULL

harry

5

KC

6

skip

7

sparky

8

NULL

NULL

sneakers

10

NULL

NULL

harvey

 

Note: NULL indicates a NULL value in the corresponding database field.

Then,

jjj=NULL_PROCESSOR(foo,['ID_NO','ANIMAL_NAME'],Comp=comp)

produces the results

jjj = 2       3       7       8       9      10
comp = 0           1           4           5           6

This output can be utilized as in the following examples.

Table2 = table(comp)  

produces a table with only rows and no missing values, as shown in Table2, or as in blanktest Data .

 

Table2

ID_NO

ANIMAL_NAME

1

golden

2

chirpy

5

KC

6

skip

7

sparky

Then,

Table3=table(jjj) 

produces a table, as shown in Table3, containing only rows with missing data (note how zeros have been substituted for values of ID_NO that are missing).

 

Table3

ID_NO

ANIMAL_NAME

3

 

0

harry

8

 

0

sneakers

10

 

0

harvey

Instead, if you want only the locations where one field is missing, a different db_sql call, jjj=foopro(foo,['ID_NO'],Comp=comp), returns an array, jjj, with the rows where ID_NO is missing (3 8 10).

Remember that rows are counted beginning with 0.