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