SAND CDBMS Tools Guide - NDL++ String Functions (LOOKUP)

SAND CDBMS Tools Reference Guide
NDL++ Functions: Syntax and Examples

Return to String Function Index

String Function Syntax and Examples

LOOKUP( )
The LOOKUP() function is used to search for a key value in either a table, the output of an SQL statement, or the fields in the RECORD or table section of a specification file. If the key value is found, LOOKUP() returns the value (or attribute) from a field in the same row as the first match. If the key value is not found, a user-specified default value is returned instead.

Syntax:

LOOKUP('lookup-name',
       'attribute-field',
      { attribute-default | NULL },
       'key-field',
        key-value[, 'key-field2',
                     key-value2[, 'key-field3',
                                   key-value3] ] )

where:

lookup-name specifies a LOOKUP instance, defined/initialized in the INIT section of the specification file. This argument must be enclosed by quotation marks.

attribute-field specifies the name of the field whose value is returned if the key value is found in the key field. This argument must be enclosed by quotation marks.

attribute-default is the default value that is returned if the lookup value(s) are not found in the key column(s). To return a null value by default, specify the NULL keyword.

key-field is the field in which to look for a key value. This argument must be enclosed by quotation marks.

key-value is the value to look up in the key column.

key-field2 is an optional second field in which to look for a key value. This second key field forms a composite lookup key with the first key field. That is, the specified values for these two key fields must be found in the same row for there to be a lookup match. This argument must be enclosed by quotation marks.

key-values2 is the value to look up in the second key column.

key-field3 is an optional third field in which to look for a key value. This third key field forms a composite lookup key with the first and second key fields. That is, the specified values for these three key fields must be found in the same row for there to be a lookup match. This argument must be enclosed by quotation marks.

key-value3 is the value to look up in the third key column.

As indicated by the syntax, this function can have five, seven, or nine parameters. There can be one, two, or three key-field/key-value pairs. Each argument, except for attribute-default and the key values, is an explicit character constant, and must be quoted. The attribute-default parameter and the key values can be character expressions.

Note:
LOOKUP() will stop searching the source data as soon as it finds a match for the specified key value(s). If the source data contain multiple occurrences of matching key value(s), LOOKUP() returns a value only for the first one it encounters. There is no way to predict what order the data will be searched. If required, it is up to the user to ensure that the data set "key" is unique.

Initializing a LOOKUP Function Instance

All instances of the LOOKUP() function must be initialized in the INIT section of the specification file. The INIT section must appear after the LIBRARY section(s) and before all other sections (TYPE, VAR, RECORD, and so on). The INIT section begins with the INIT keyword and contains a list of one or more LOOKUP instance (or library) initialization statements, all between braces { }.

A LOOKUP instance declaration provides the following information: the lookup source, a list of attribute fields, and a list of key fields. Since a LOOKUP call can only use attribute and key fields that are declared in the INIT section, ensure that all fields that will be used in the script are accounted for in the initialization. At the same time, all unused attribute and key fields should be removed from the function declaration.

The function declaration has the following format:

LOOKUP 'lookup-name'
{
  [ LOOKUPBUFFER : buffer-size ]
    <source parameters>
    ATTRIBUTES   : attribute-fields-list
    KEYS         : key-fields-list
}

where:

lookup-name is the name of the LOOKUP instance. Any LOOKUP() function that makes use of this particular instance must reference the instance name. Any name can be selected for the lookup-name argument, but it must be unique among all LOOKUP names in the script. Note that the name must be between quotation marks (' ').

LOOKUPBUFFER: buffer-size is the optional user-specified buffer size for the LOOKUP instance, in megabytes (MB). By default, the LOOKUP buffer is set to 5 MB. To potentially improve performance, this value can be set as high as 2048 MB (2 GB) minus 1 byte. If the value is set higher, it will be adjusted to the maximum of 2048.

default: 5 MB
maximum: 2048 MB (2 GB) - 1 byte

ATTRIBUTES: attribute-fields-list is the list of one or more fields from the source that can be referenced as attribute fields in applications of the LOOKUP() function. Each attribute field in the list must be contained in quotation marks (' '), and multiple attribute fields must be separated from each other by commas ( , ).

KEYS: key-fields-list is the list of one, two, or three fields from the source that can be referenced as key fields in applications of the LOOKUP() function. Each key field in the list must be contained in quotation marks (' '), and multiple key fields must be separated from each other by commas ( , ). When more than one key field is included in the function declaration, a composite key can be specified in LOOKUP() instances. That is, the LOOKUP() function will search the source data for the first row that contains each of the specified key values.

The order in which multiple keys are listed in the key fields list is important, as only the first key field, the first and second key fields together, or all three key fields together can be specified in LOOKUP() instances. For instance, if the LOOKUP declaration contains this key field list:

    KEYS: 'col1', 'col2', 'col3'

The following are the only permitted LOOKUP() keys:

col1

col1, col2 (composite key)

col1, col2, col3 (composite key)

<source parameters>

SQL : <SELECT statement>;
The source can be the output of any SQL SELECT statement executed against the current database. The only restriction is that an ORDER BY clause cannot appear in the statement. Note that the SELECT statement must be terminated with a semicolon ( ; ).

TABLE : 'table-name'
The source can be a single table that exists in the current database. If the table is not in the default schema, prefix the table name with the name of the schema to which it belongs, for example, 'schema1.table1'. The table-name argument must appear within quotation marks (' ').

SCRIPT : @specification-script [ COLUMN | TABLE ] [ '<ndl parameters>' ]
The LOOKUP initialization can involve a specification script that describes an IMPORT or ODBCIMPORT operation. The contents of the referenced data file(s), including any NDL++ processing, are used as the actual lookup source. Prefix the specification file name with the ‘@’ symbol.

There are two optional SCRIPT modes: COLUMN and TABLE.

In COLUMN mode (the default), the LOOKUP() function uses the data described in the RECORD section as the lookup source.

In TABLE mode, the LOOKUP() function uses the data described in the map specification section as the lookup source.

In addition, some of the ndlm command line flags may be included with the SCRIPT parameter, to allow the user extra control over how the data is to be processed. The available ndlm flags are the following (consult the Running ndlm section for further details):

-b
-e file-name
-f buffer-size
-i bytes
-l
-n null-string
-o {0 | 1}
-q
-s rows
-z {0 | 1}

If a flag not in the above list is included, or a flag value is illegal, processing of the LOOKUP() function will not be halted, but the rest of the flag string will be ignored.

All flags and their values must have surrounding quotation marks, for example:

'-f 1024 -b -o 1'

Example (INIT section)

INIT
{

LOOKUP 'L1'
{
    SQL        : SELECT * FROM T;
    ATTRIBUTES : 'A1', 'A2', 'A3'
    KEYS       : 'K1', 'K2'
}

LOOKUP 'L2'
{
    LOOKUPBUFFER : 64
    TABLE       : 'T'
    ATTRIBUTES   : 'A1', 'A2', 'A3'
    KEYS         : 'K1', 'K2'
}

LOOKUP 'L3'
{
    LOOKUPBUFFER : 2048
    SCRIPT       : @scriptfile1 COLUMN '-f 1024 -b -o 0'
    ATTRIBUTES   : 'A1', 'A2', 'A3'
    KEYS         : 'K1', 'K2'
}

LOOKUP 'L4'
{
    SCRIPT     : @scriptfile1 TABLE '-f 1024 -b -o 1'
    ATTRIBUTES : 'A1', 'A2', 'A3'
    KEYS       : 'K1', 'K2'
}

}

Example (SELECT statement)

IMPORT @load.dat
{
    INIT
    {
        LOOKUP 'SColors'
        {
            LOOKUPBUFFER : 1024
            SQL          : SELECT C.ColorName, C.ColorIndex, C.ColorPrice
                           FROM Colors C, Inventory I
                           WHERE C.ColorIndex = I.ColorIndex
                           AND I.Qty > 0 ;
            ATTRIBUTES   : 'ColorIndex', 'ColorPrice'
            KEYS         : 'ColorName'
        }
    }
    RECORD
    {
        field1 *\r\n
    }
    colortable
    {
        ColorIndex VALI(LOOKUP('SColors', 'ColorIndex', '-1',
                               'ColorName', field1) )
        ColorPrice VALF(LOOKUP('SColors', 'ColorPrice', '0',
                               'ColorName', field1) )
    }
}

In this sample script, a LOOKUP instance with the name 'SColors' is declared in the INIT section, with a buffer size set to 1024 MB (1 GB). The instance uses a SELECT statement, executed against the current database, as its source. Two of the SELECT columns, 'ColorIndex' and 'ColorPrice', are listed as potential attribute fields, meaning that any LOOKUP() function in the script that references the 'SColors' instance must return an attribute from one of those two columns. Only the column 'ColorName' is listed as a key field, which means that all occurrences of the LOOKUP() function that use the 'Colors' instance must search for the key value in the 'ColorName' column only.

In the example, the first LOOKUP call returns an attribute from the ‘ColorIndex’ column if the key value (field1) is found in the 'ColorName' column; otherwise it returns '-1'. The second LOOKUP call returns an attribute from the 'ColorPrice' column if the key value (field1) is found in the 'ColorName' column; otherwise it returns '0'.

Example (TABLE)

IMPORT @load.dat
{
    INIT
    {
        LOOKUP 'TColors'
        {
            LOOKUPBUFFER : 1024
            TABLE : 'Colors'
            ATTRIBUTES : 'ColorIndex', 'ColorPrice'
            KEYS : 'ColorName'
        }
    }
    RECORD
    {
        field1 *\r\n
    }
    colortable
    {
        ColorIndex VALI(LOOKUP('TColors', 'ColorIndex', '-1',
                               'ColorName', field1) )
        ColorPrice VALF(LOOKUP('TColors', 'ColorPrice', '0',
                               'ColorName', field1) )
    }
}

In this script, which is a variation of the SELECT example, a LOOKUP instance with the name 'TColors' is declared in the INIT section. Instead of specifying a SELECT statement as its lookup source, this script specifies a single table (Colors). A SELECT statement is automatically generated and applied against the database, using the fields listed under Attributes and Keys as projections from the specified table. In this case, the SQL would be the following:

SELECT ColorIndex, ColorPrice, ColorName
FROM Colors;

Table mode should be used only when the lookup source is a single table, and the intended SQL is a simple, unfiltered SELECT from that table.

Return to String Function Index

SAND CDBMS Tools Reference Guide NDL++ Functions: Syntax and Examples

SAND CDBMS Tools Reference Guide
NDL++ Functions: Syntax and Examples