Element Details: Lookup.SQL
 Back


Available in: Logi Info Source code name: Lookup.SQL

Lookup is used to join the rows of a DataLayer with data from another data source.

Unlike a standard Join element, a Lookup element does not require that all of the target data is downloaded prior to the join. Instead Lookup elements will execute the query once for each row of data. This is ideal when trying to join small data sets to very large data sets.

To define command parameters, use the "SqlParameters" element. The standard @Data token is used to access column values.

All of the returned data rows are added to the DataLayer. Use the MaxRows attribute to limit the number of records added for each query.

If the "ExceptionDataID" attribute has a value, any records in the DataLayer that do not have a match will be moved into a separate DataLayer. If left blank, those records will remain in the original DataLayer.

Element Group:Merge and Join



ATTRIBUTES

Click attribute Name to drill down for more information.

NamePossible ValuesDescription
ConnectionID
UserDefined
Specifies a connection to a data source that is defined in the Settings. For elements connecting to relational databases, default is the first Connection element in _settings.lgx.
ExceptionDataID
UserDefined
Defines an ETL ID value that can be used to address the exception data that was removed by the filter. The value of this attribute may be referenced by a DataLayer.ETL, LoadDbTable, SaveData, or any other element that supports the EtlElementID attribute.
ID
UserDefined
(Required) The ID attribute is a pervasive attribute that uniquely identifies an element within a definition file. The ID needs to be a unique value within the definition.
MaxRows
UserDefined
The maximum number of rows to retrieve from the data source.
Source
UserDefined
(Required) A SQL statement that returns a set of records. Use tokens, such as @Request and @Session, inside of the SQL command to control the result set.



PARENT ELEMENTS

Click element to drill down for more information.

DataLayer.Bookmarks
DataLayer.Cached
DataLayer.CSV
DataLayer.Definition List
DataLayer.Directory
DataLayer.ETL
DataLayer.Excel
DataLayer.Fixed Format File
DataLayer.Google App
DataLayer.Google Spreadsheet
DataLayer.GPX File
DataLayer.JSON
DataLayer.KML File
DataLayer.LDAP
DataLayer.Linked
DataLayer.Plugin
DataLayer.REST
DataLayer.Scheduler
DataLayer.SimpleDB
DataLayer.SP
DataLayer.SQL
DataLayer.Static
DataLayer.Twitter
DataLayer.Web Feed
DataLayer.Web Scraper
DataLayer.Web Service
DataLayer.XML


CHILD ELEMENTS

Click element to drill down for more information.

SQL Parameters


 Back to top


 Chart Debug