Generating a SQL statement from two tables

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.

  INSERT INTO PUBLIC.Table_3(COL01,COL02,COL03,COL04)(
     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.