Lookup Heater, Selecting fields from database tables
With the Lookup Heater database fields are dynamically interrogated during the transformation on either the READ or WRITE side. The contents/data of the interrogated database fields can be processed in the Definition any way you like.
Side:Here you can select from which side of the Definition a database lookup is to be carried out.
Adapter: When several Adapters are available in the Definition you can select here which Adapter (data source) to run the database lookup against. By default the first Adapter available will be used.
Table: Database table or view name that contains the required information.
Field: The table field that the Lookup Heater shall deliver.
Where: The "Where" clause in a Select statement (omitting the word "Where" itself). In this clause the variables $1 ... $x represent the incoming parameters of the Heater.
$1 = first parameter, $2 = second, etc...
OrderBy: Optionally a list of comma separated field names to determine the sort sequence of a database query result that produces more than one record. Note: Only the data from the first applicable record is actually returned
e.g. LastName, FirstName
By default an ascending sorted sequence is assumed. To specify a descending sort sequence instead, it is necessary to add the word DESC after any field(s) to be treated this way.
e.g.LastName DESC, FirstName DESC
In the depicted example (above) the Lookup Heater is configured so that for each record/row on the READ side the FirstName field is obtained from the table t_Adress from the database on the WRITE side, with the same ID as the current record on the READ side. Of course, the READ field ID must be connected to the first incoming parameter of the Lookup Heater.
Default value: If you check the option “In cases where no value is available, return this value” you will then be permitted to enter a default value to specify what will result when the database lookup finds nothing.
- The Lookup Heater supports all (text) input values, including FlowHeater Parameters and environment variables as placeholders.
- Use the SQL Heater instead for more flexible database lookups, including across several tables (via JOIN).
- Perhaps you wish to add text data from a CSV file to existing SQL database fields. For example a memo or text commentary field is not to be overwritten, rather extra text added to the end of any existing text in the field. In this case the field would first be elicited using the Lookup Heater and the new text appended using a String Append Heater to the end of the existing text before overwriting the field with the combined text.