Transform SQL Step

The Transform SQL step uses the SQL language to include, delete, reorder, and rename columns, modify data types, group data, or create calculation expressions and operations on the information source within the data preparation flow. The step is located in the Preparation category of the toolbar.

How to configure

The configuration is straightforward: connect one or more steps to the input port and the step is ready to use. The screen areas are:

Connected Tables: displays the tables connected to the input port, which can be used in SQL queries. You can copy the query for a connected table by clicking the copy icon next to it. If any data is changed in the previous step (column or table deletion, or data type change), the system generates an error indicating an invalid column.
SQL Editor: a field where you write the desired query and click TEST. The system executes the query and displays the results for verification.
Close: closes the screen without saving and returns to the Designer.
Save: saves the settings and updates the data sample.

Table with inconsistency: if any inconsistency is found in a connected table, remove it using the REMOVE button, displayed within the Transform SQL step.

Configuration example

Consider the initial table TITULOSFINANCEIROS, with the columns Customer Code, Customer Name, Document Number, and Value. For example: Customer A has two documents (100.00 and 200.00) and Customer B has two documents (120.00 and 80.00).

To sum and group values by customer, use the query:

SELECT
  T."Código Cliente",
  T."Nome Cliente",
  SUM(T."Valor") AS "Valor Total"
FROM "TITULOSFINANCEIROS" T
GROUP BY
  T."Código Cliente",
  T."Nome Cliente"

The result is the total value of the documents per customer: Customer A with 300.00 and Customer B with 200.00. The example shows how to use the step for data aggregation and summarization operations.

Was this helpful?