DB_LOOKUP: Retrieving Data Source Values
Available Languages: reporting, MODIFY
You can use the DB_LOOKUP function to retrieve a value from one data source when running a request against another data source, without joining or combining the two data sources.
DB_LOOKUP compares pairs of fields from the source and lookup data sources to locate matching records and retrieve the value to return to the request. You can specify as many pairs as needed to get to the lookup record that has the value you want to retrieve. If your field list pairs do not lead to a unique lookup record, the first matching lookup record retrieved is used.
DB_LOOKUP can be called in a DEFINE command, TABLE COMPUTE command, MODIFY COMPUTE command, or ibi Data Migrator flow.
There are no restrictions on the source file. The lookup file can be any non-FOCUS data source that is supported as the cross-referenced file in a cluster join. The lookup fields used to find the matching record are subject to the rules regarding cross-referenced join fields for the lookup data source. A fixed-format sequential file can be the lookup file if it is sorted in the same order as the source file.
Retrieve a Value From a Lookup Data Source
DB_LOOKUP(look_mf, srcfld1, lookfld1, srcfld2, lookfld2, ..., returnfld);
where:
Is the lookup Master File.
Are fields from the source file used to locate a matching record in the lookup file.
Are columns from the lookup file that share values with the source fields. Only columns in the table or file can be used; columns created with DEFINE cannot be used. For multi-segment synonyms, only columns in the top segment can be used.
Is the name of a column in the lookup file whose value is returned from the matching lookup record. Only columns in the table or file can be used; columns created with DEFINE cannot be used.
Usage Notes for DB_LOOKUP
- The maximum number of pairs that can be used to match records is 63.
- If the lookup
file is a fixed-format sequential file, it must be sorted and retrieved
in the same order as the source file, unless the ENGINE INT SET
CACHE ON command is in effect. Having this setting in effect may
also improve performance if the values will be looked up more than
once. The key field of the sequential file must be the first lookup field
specified in the DB_LOOKUP request. If it is not, no records will
match.
In addition, if a DB_LOOKUP request against a sequential file is issued in a DEFINE FILE command, you must clear the DEFINE FILE command at the end of the TABLE request that references it, or the lookup file will remain open. It will not be reusable until closed and may cause problems when you exit. Other types of lookup files can be reused without clearing the DEFINE. They will be cleared automatically when all DEFINE fields are cleared.
- If the lookup field has the MISSING=ON attribute in its Master File and the DEFINE or COMPUTE command specifies MISSING ON, the missing value is returned when the lookup field is missing. Without MISSING ON in both places, the missing value is converted to a default value (blank for an alphanumeric field, zero for a numeric field).
- Source records display on the report output even if they lack a matching record in the lookup file.
- Only real fields in the lookup Master File are valid as lookup and return fields.
- If there are multiple rows in the lookup table where the source field is equal to the lookup field, the first value of the return field is returned.
Retrieving a Value From a Fixed Format Sequential File in a TABLE Request
The following procedure creates a fixed-format sequential file named GSALE from the GGSALES data source. The fields in this file are PRODUCT (product description), CATEGORY (product category), and PCD (product code). The file is sorted on the PCD field:
SET ASNAMES = ON TABLE FILE GGSALES SUM PRODUCT CATEGORY BY PCD ON TABLE HOLD AS GSALE FORMAT ALPHA END
The following Master File is generated as a result of the HOLD command:
FILENAME=GSALE, SUFFIX=FIX , $
SEGMENT=GSALE, SEGTYPE=S1, $
FIELDNAME=PCD, ALIAS=E01, USAGE=A04, ACTUAL=A04, $
FIELDNAME=PRODUCT, ALIAS=E02, USAGE=A16, ACTUAL=A16, $
FIELDNAME=CATEGORY, ALIAS=E03, USAGE=A11, ACTUAL=A11, $
The following TABLE request against the GGPRODS data source, sorts the report on the field that matches the key field in the lookup file. It retrieves the value of the CATEGORY field from the GSALE lookup file by matching on the product code and product description fields. Note that the DEFINE FILE command is cleared at the end of the request:
DEFINE FILE GGPRODS
PCAT/A11 MISSING ON = DB_LOOKUP(GSALE, PRODUCT_ID, PCD,
PRODUCT_DESCRIPTION, PRODUCT, CATEGORY);
END
TABLE FILE GGPRODS
PRINT PRODUCT_DESCRIPTION PCAT
BY PRODUCT_ID
END
DEFINE FILE GGPRODS CLEAR
END
Because the GSALE Master File does not define the CATEGORY field with the MISSING=ON attribute, the PCAT column displays a blank in those rows that have no matching record in the lookup file:
Product Code |
Product |
PCAT |
------- |
------- |
---- |
B141 |
Hazelnut |
|
B142 |
French Roast |
|
B144 |
Kona |
|
F101 |
Scone |
Food |
F102 |
Biscotti |
Food |
F103 |
Croissant |
Food |
G100 |
Mug |
Gifts |
G104 |
Thermos |
Gifts |
G110 |
Coffee Grinder |
Gifts |
G121 |
Coffee Pot |
Gifts |
If you add the MISSING=ON attribute to the CATEGORY field in the GSALE Master File, the PCAT column displays a missing data symbol in rows that do not have a matching record in the lookup file:
Product Code Product PCAT ------- ------- ---- B141 Hazelnut . B142 French Roast . B144 Kona . F101 Scone Food F102 Biscotti Food F103 Croissant Food G100 Mug Gifts G104 Thermos Gifts G110 Coffee Grinder Gifts G121 Coffee Pot Gifts
Retrieving a Value From a LOOKUP Table
DB_LOOKUP takes the value for STORE_CODE and retrieves the STORENAME associated with it.
DB_LOOKUP(dmcomp,STORE_CODE,STORE_CODE,STORENAME)
For 1003CA the result is Audio Expert.
For 1004MD the result is City Video For 2010AZ the result is eMart.