Random Lookup Function

The Random Lookup Function selects a value at random from a specified lookup table to insert in a destination column. The function generates a random number between 1 and the limit or number of rows in the lookup table to use as a subscript into the table. The column value or values from the row that correspond to the subscript are inserted in the destination column.

There are two forms of the Random Lookup Function, single column and multiple column. The single column form inserts a value into a single destination column. The multiple column form inserts values from multiple lookup table columns into corresponding destination columns.

You can enter the multiple column Random Lookup Function for any source column that will be replaced by a lookup table value, but you must edit the Column Map to remove the names of remaining source columns that will also be replaced.

The IGNORE parameter allows you to ignore the lookup table and use a source value when a row in a specified source column contains a specified value (NULL, SPACES (for CHAR columns), or zero-length VARCHAR).

You can use the PRESERVE parameter to ignore the lookup table and use a source value when a row in a specified source column contains a specified value (NULL, SPACES (for CHAR columns), or zero-length VARCHAR).

The syntax is:

RAND_LOOKUP(lktablename, { columnname | dest=(col1,coln) ,values=(col1,coln) }
[,limit ] [,ignore=(col(spaces, null, zero_len), )
| PRESERVE=( colname (spaces, null, zero_len), ) ] )
lktablename
Name of the lookup table. You may specify the lookup table name as dbalias.creatorid.tablename, creatorid.tablename, or tablename. If the table name is not fully qualified, destination table qualifiers are used.
columnname
Name of the column in the lookup table that contains the values to be randomly selected for insertion at the destination. (Required for single column lookup.)
dest=
Names of the destination table columns in which values from the lookup table are inserted. (Required for multiple column lookup.)
col1,coln
Destination table column names. The order of the column names must correspond to the lookup table columns in the values= parameter.
values=
Names of the columns in the lookup table that contain values to be inserted at the destination. (Required for multiple column lookup.)
col1,coln
Lookup table column names. The order of the column names must correspond to the destination table columns in the dest= parameter.
limit
Optional limit on number of rows from the lookup table used to select column values. Specify an integer, up to a maximum value of 2,000,000,000. If no limit is specified, all rows are used.
Note: A table of column values is generated in memory. The size of this table may be limited by system resources.
ignore=
List of source columns with values that are inserted at the destination instead of the lookup value when the column has a row with a stated value (NULL, SPACES, or zero-length VARCHAR).
col
The source column name.

For single column lookup, enter one column name only.

For multiple column lookup, the order of the column names must correspond to the destination table columns in the dest= parameter. The number of columns must equal the columns in the dest= parameter, and at least one column must include values. To not specify values for a column, do not enter a value. For example, coln().

null
Ignore the lookup table if the source column row has a NULL value.
spaces
Ignore the lookup table if the source column row has a SPACES value. For CHAR columns only.
zero_len
Ignore the lookup table if the source column row has a zero-length VARCHAR value.
PRESERVE=
List of source columns with values that are inserted at the destination instead of the lookup value when the column contains a stated value (NOT_FOUND, null, spaces, or zero-length varchar).
NOT_FOUND
Ignore the lookup table if no match is found for the source column row.
Note:

Preserve= and ignore= are mutually exclusive. Ignore= will be deprecated in a future release.

The col, null, spaces, and zero-length varchar operands have the same effect when used with either preserve= or ignore=. Details on these operands are shown above with ignore=.

Single Column Example

To select a value at random from the STATE column in the first 50 rows of a table named STATE_LOOKUP and insert it in the destination column, specify:

RAND_LOOKUP(STATE_LOOKUP,STATE,50)

Multiple Column Example

To select values from the CITY, STATE, and ZIPCODE columns in a random row of a table named STATE_LOOKUP and insert them in the corresponding destination columns, specify:

RAND_LOOKUP(STATE_LOOKUP,
DEST=(CITY,STATE,ZIPCODE),
VALUES=(CITY,STATE,ZIP))

Ignore Example

Use the following statement to extend the Single Column Example, where the source column is named STATES and you want to use the source NULL and SPACES values instead of lookup table values:

RAND_LOOKUP(STATE_LOOKUP,STATE,50, IGNORE=(STATES(NULL,SPACES)))