Not available in Community Designer

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

Data records unloaded from 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 database, or you can cache records found in database as well as records not found in database. To save both, use Store negative key response checkbox. Then, 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 wizard, choose Database lookup radio button and click Next.

Figure 34.11. Database Lookup Table Wizard

Then, in the Database lookup table wizard, give a Name to the selected lookup table, specify some 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 indispensable to specify Metadata.

Type or edit some SQL query that serves to look up data records from 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 some 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 e.g. "EMPLOYEE_ID". Records matching the given key 'replace the question mark character in the query.

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