DataFlux Data Management Studio 2.6: User Guide
You can add an SQL Lookup node to a data job to enable you to find rows in a database table that have one or more fields matching those in the data job. SQL lookup processing provides an explicit advantage with performance, especially with large databases.
Under SQL lookup, a large database is not copied locally on the hard drive in order to perform the operation (as is the case with joins). Instead, SQL queries are used to extract the matching row (or rows). For quick comparisons against large databases, SQL lookup is very convenient in terms of performance when the fields that are being looked up in the table are indexed. The SQL lookup step is equivalent to doing an SQL left-outer join.
Once you have added the node, you can double-click it to open its properties dialog. The properties dialog includes the following elements:
Name - Specifies a name for the node.
Notes - Enables you to open the Notes dialog. You use the dialog to enter optional details or any other relevant information for the node.
Lookup Table - Enables you to specify the table used when running the comparisons against the incoming data input fields from the previous step. You can also click Browse to display a list of data sources that have been established through Open Database Connectivity (ODBC).
The Field mappings section of the properties dialog includes the following elements:
Input table - Displays the incoming data input fields from the previous step in the data job.
Lookup table - Displays the available fields in the lookup table selected earlier.
Expressions - Displays the node's field mapping expressions. Click to map the selected input table's field to the selection lookup table's field. The resulting expression will appear in the Expressions list. You can click OR to modify the expressions.
Single Matches Only - When selected, discontinues the matching process after the first match is found.
Only output records that have a match - When selected, constrains the output to contain only records that have a match.
The Output fields section of the properties dialog includes the following elements:
Available - Displays the fields that you can make available for the next step in your data job. Items displayed in this list are dependent on your data sources and any preceding steps in your data job.
Selected - Displays the fields that will be made available to the next node in your data job.
You can access the following advanced properties by right-clicking the SQL Lookup node:
|
Documentation Feedback: yourturn@sas.com
|
Doc ID: dfU_PFUtil_SQLLookup.html |