Lookup Table - Connect_CDC - connect_cdc_mimix_share - 5.8

Connect CDC System Reference Guide

Product type
Software
Portfolio
Integrate
Product family
Connect
Product
Connect > Connect CDC (MIMIX Share)
Version
5.8
Language
English
Product name
Connect CDC
Title
Connect CDC System Reference Guide
Copyright
2024
First publish date
2003
ft:lastEdition
2024-02-12
ft:lastPublication
2024-02-12T15:11:59.486869

Method:   Lookup Table

Function name:   LookupDatabaseTable

Parameter list:   4 CHAR inputs: see description below.

Return type:   CHAR - string value containing the value from the row fetched from the lookup table

What the method does:    Takes an input key value derived from a source column or constructed using an expression and looks up this key value in a lookup table fetching the row associated with this value, and then returns the value of some other column in the lookup table. The lookup table must exist in the same server as the associated source table.

The 4 CHAR inputs are described as follow:

CHAR - String containing the fully qualified name of the lookup table, including database name, if appropriate, whereby the qualifiers are separated by periods, for example:

   dbtest.dbo.test_tab

   dave.my_table

CHAR - String containing the column name in the lookup table that contains the key value used for selecting a row in the lookup table.

CHAR - String containing the column name in the lookup table that contains the lookup value that you want to return from the one row selected in the lookup table

CHAR - String containing the key value that you want to look up in the lookup table. This can be specified in any way that is acceptable syntax in the expression language. For example, this could be simply the name of a column in the associated source table or this could be some expression that constructs the desired key value in some way.)

The fourth input argument is type CHAR and the return type is CHAR. If one wants to use this method with a numeric input or output value, then the appropriate expression language conversion routine must be used (for example, tonumber() ) to convert the value appropriately.

Also, if the method cannot find a row in the lookup table for the input key value, the method returns a one byte character value of ? to indicate that a row was not selected for the input key value.

Examples:

LookupDatabaseTable('dbprod.dbo.customers', 'cust_no', 'cust_name', CUSTOMER_NUMBER);

LookupDatabaseTable('PROD.PARTS', 'PART_NO', 'PART_DESC', concat('PRD-', part_number) );.