Lookup Table Functions
In your graphs you are also using lookup tables. You need to use
them in CTL by specifying the name of the lookup table and placing it as an
argument in the lookup() function.
![[Warning]](figures/warning.png) | Warning |
|---|---|
Remember that you should not use the functions shown below
in the |
Now, the key in the function below is a sequence of values of
the field names separated by comma (not semicolon!). Thus, the key is
of the following form:
keyValuePart1,keyValuePart2,...,keyValuePartN.
See the following options:
lookup(<lookup name>).get(keyValue)[.<field name>|.*]This function searches the first record whose key value is equal to the value specified in the
get(keyValue)function.It returns the record of the lookup table. You can map it to other records in CTL2 (with the same metadata). If you want to get the value of the field, you can add the
.<field name>part to the expression or.*to get the values of all fields.lookup(<lookup name>).count(keyValue)If you want to get the number of records whose key value equals to
keyValue, use the syntax above.lookup(<lookup name>).next()[.<field name>|.*]After getting the number of duplicate records in lookup table using the
lookup().count()function, and getting the first record with specified key value using thelookup().get()function, you can work (one by one) with all records of lookup table with the same key value.You need to use the syntax shown here in a loop and work with all records from lookup table. Each record will be processed in one loop step.
The mentioned syntax returns the record of the lookup table. You can map it to other records in CTL2 (with the same metadata). If you want to get the value of the field, you can add the
.<field name>part to the expression or.*to get the values of all fields.
Example 62.8. Usage of Lookup Table Functions
//#CTL2
// record with the same metadata as those of lookup table
recordName1 myRecord;
// variable for storing number of duplicates
integer count;
// Transforms input record into output record.
function integer transform() {
// if lookup table contains duplicate records,
// their number is returned by the following expression
// and assigned to the count variable
count = lookup(simpleLookup0).count($0.Field2);
// getting the first record whose key value equals to $0.Field2
myRecord = lookup(simpleLookup0).get($0.Field2);
// loop for searching the last record in lookup table
while ((count-1) > 0) {
// searching the next record with the key specified above
myRecord = lookup(simpleLookup0).next();
// incrementing counter
count--;
}
// mapping to the output
// last record from lookup table
$0.Field1 = myRecord.Field1;
$0.Field2 = myRecord.Field2;
// corresponding record from the edge
$0.Field3 = $0.Field1;
$0.Field4 = $0.Field2;
return 0;
} | Warning |
|---|---|
In the example above we have shown you the usage of all lookup table functions. However, we suggest you better use other syntax for lookup tables. The reason is that the following expression of CTL2: lookup(Lookup0).count($0.Field2); searches the records through the whole lookup table which may contain a great number of records. The syntax shown above may be replaced with the following loop: myRecord = lookup(<name of lookup table>).get(<key value>);
while(myRecord != null) {
process(myRecord);
myRecord = lookup(<name of lookup table>).next();
}Especially DB lookup tables can return -1 instead of real count of records with specified key value (if you do not set Max cached size to a non-zero value). The |
![[Important]](figures/important.png) | Important |
|---|---|
Remember that DB lookup tables cannot be used in compiled mode! (code starts with the following header: You need to switch to interpreted mode (with the header: |