It is possible to directly call queries and SQL instructions in the source database from the source transformation (selection/transformation on the files of the model), and in the target database from the transformation on the target (transformation in the files distributed).
The syntax is in the same format as the database you are accessing.
Lookup
Lookup allows to call a query and one line.
If many lines are shown, only the first one is recovered.
Lookup Alias As Query Alias is the name for the set of data that are retrieved. It will also be used to fix all the columns retrieved in the buffer F.
As soon as the instruction Lookup is entered, a buffer F followed by an alias name is created and contains all the retrieved columns.
The query shown in Query goes to the database engine to be validated.
To show the variables (different buffer fields) in the query, you need to mark them with #.
Example:
Lookup Customer As Select LastName from Adventure..DimCust _
where CustKey=#I.Customer# and Company=#W.Company#The Lookup command is multiline.
Each line, except for the last one, needs to end with the underscore symbol ( _ ).
SQL
This parameter executes an SQL Statement without data returned.
SQL Statement The command entered in Statement is not validated during the entry. Possible errors in the command are detected during the execution.
To show the different variables of the SQL command (different buffer fields) they need to be marked with #.
Example:
SQL Update Adventure..DimCust Set Used=1 _
where CustKey=#I.Customer# and Company=#W.Company#The SQL command is multiline, so each line, except the last one, needs to finish with the underscore symbol (_).