Skip to main content

Generating a SQL statement from two tables

This article introduces a sample Job that generates a SQL statement from two tables using the tELTInput, tELTMap, and the tELTOutput components and then displays the generated SQL statement using tWarn.

To generate a SQL statement from multiple database tables, connect multiple tELTInput components to a tELTMap component as the input of the tELTMap component, with a database table specified in each of the tELTInput component; the tELTMap component is then connected to a tELTOutput component. You need to define table column mapping and mapping conditions in the ELT map editor of the tELTMap component, which are passed to the tELTOutput component as the output. The tELTOutput component generates the SQL statement according to the output of the tELTMap component.

The key settings of the components in this Job are as follows.

  • For tELTInput_1, Table_1 is specified as the input table. The table has three DECIMAL-type columns: COL1, COL2, and COL3.
  • For tELTInput_2, Table_2 is specified as the input table. The table has two DECIMAL-type columns: COLUMN1 and COLUMN2.
  • For tELTMap_1, the mapping among the table columns is set in the ELT map editor of the component, as shown in the following figure.
  • For tELTOutput_1, the key settings are shown in the following figure (pay attention to those in red frames).
    With all the above settings, the result SQL statement will be the one listed below. The result SQL statement will be stored in the QUERY variable of tELTOutput_1.
       SELECT Table_1.COL1, Table_1.COL2, Table_1.COL3, Table_2.COLUMN2
          FROM Table_1 INNER JOIN  Table_2 ON(Table_2.COLUMN1 = Table_1.COL1))
    Information noteNote: The actual SQL statement generated is subject to change without prior notice.
  • The tWarn_1 component displays the SQL statement generated in the console. To achieve this, enter ((String)globalMap.get("tELTOutput_1_QUERY")) in the Warn message field and optionally select Info from the Priority drop-down list.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!