Lookup tables improve query execution by performing an often-used pattern of query processing with a single access to the database.
Lookup patterns are characterized by a translation from one form of data (typically an index number) into another form (typically a text string, which is more readable.) For example, a “customers” table often has the title (or salutation) encoded as a numeric. The numeric is a key field to a “title” table, which contains a full description of the title. In this case, you could load a lookup table with the contents of the “title” table. The master query producing the list of customers would then access the lookup table to perform the translation to a readable title for the customer.
Although you could perform this type of operation with a join, this is often not practical, since the master query may already contain many joins, and to perform this additional one would consume excessive resources.
The following topics discuss how to create and use lookup tables in SQR Production Reporting Studio. As we discuss creating and using lookup tables, we will load a lookup table with “customer number” as the key field. We will then create a variable to read the customer number and return the customer name.