This type of lookup table works with databases and unloads data from them by using a SQL query. Database lookup table reads data from the specified database table. The key used to search records from this lookup table is the where fieldName = ? [and ...] part of the query.

Data records unloaded from the database can be cached in memory keeping the LRU order (the least recently used items are discarded first). To cache them, you must specify the number of such records (Max cached records).

You can cache only the record found in the database, or you can cache both records found as well as records not found in the database. To save both, use Store negative key response checkbox. Then, the lookup table will not search through the database table when the same key value is given again.

Database lookup table allows to work with duplicate records (multiple records with the same key value).

Creating Database Lookup Table

In the first step of the wizard, choose the Database lookup radio button and click Next.

Figure 33.7. Database Lookup Table Wizard

Then, in the Database lookup table wizard, give a Name to the selected lookup table, and specify Metadata and DB connection.

	Note
	Remember that Metadata definition is not required for transformations written in Java. In them, you can simply select the no metadata option. However, with CTL it is necessary to specify Metadata.

Type or edit a SQL query that serves to look up data records from the database. When you want to edit the query, click the Edit button and, if your database connection is valid and working, you will be presented with the Query wizard, where you can browse the database, generate a query, validate it and view the resulting data.

	Important
	To specify a lookup table key, add a "where fieldName = ? [and ...]" statement at the end of the query, fieldName being, for example, "EMPLOYEE_ID". Records matching the given key replace the question mark character in the query.

Then, you can click OK and Finish. See Extracting Metadata from a Database for more details about extracting metadata from a database.