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 DataMigrator 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.
DB_LOOKUP(look_mf, srcfld1, lookfld1, srcfld2, lookfld2, ..., returnfld);
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.
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.
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:
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