DB_LOOKUP: Retrieving Data Source Values

How to:

Reference:

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.

Syntax: How to Retrieve a Value From a Lookup Data Source 

DB_LOOKUP(look_mf, srcfld1, lookfld1, srcfld2, lookfld2, ..., returnfld);

where:

look_mf

Is the lookup Master File.

srcfld1, srcfld2 ...

Are fields from the source file used to locate a matching record in the lookup file.

lookfld1, lookfld2 ...

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.

returnfld

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.

Reference: 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.

Example: 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