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), ) ] )
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().
preserve= and ignore= are mutually exclusive. ignore= will be deprecated in a future release.
The col, null, spaces, and zero_len operands have the same effect when used with either preserve= or ignore=.
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)
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))
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)))