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)))