Selecting columns from one or more tables

The Select action allows you to choose columns from a single table or multiple joined tables to produce an output record set that can be written to a new or existing table. You can:

• Use Table mapping to define relationships that indicate which records are included (Join types).
• Use a computed field to create an expression that computes a value using fields from one or more tables.
• When using an existing table as the output, use field mapping to map output fields to the existing table structure.

On the Configuration tab:

1. Create mappings to identify matching fields between input tables.

2. Right click on the join link to change the join type.

3. View the execution tree or enable leafing audit.

4. Use the dropdown to filter Table Fields by table from the set of joined tables.

5. Use Table Fields or Output Fields to create the filter limiting joined records.

6. Drag fields from Table Fields or select from Table Mapping.

7. Edit the field name if necessary.

8. Use Computed Field to add the expression as a field.

9. Preview data or SQL results.

Possible applications include:

• Obtain a desired subset of records from a table
• Combine records from multiple related tables by defining relationships between the tables
• Perform calculations on fields across multiple tables
• Transfer records from one database connection to another

Select columns for a new record set

1. Drag the Select icon onto the design surface.
2. On the Connections tab , enter a Name and a Description to identify this action, then specify the input tables and output options for the join:

   Input -

   • For Database Connection, select the database from which the tables originate, or choose New Database Connection to establish a new connection to another database.
   • Table Name – Select the tables to be included as input, which display in the list box below this field. Select a table in the list and click the Delete icon to remove it from the list.

   Output -

   • For Database Connection, select the database of the table to which the selected rows and columns are being written, and then enter or select a Table Name depending on the Output Mode you choose -
   • For Create/overwrite a table, enter the name of the table being created or overwritten. Using this mode, you can also choose to -
     • Add a new _autokey field to the table, with incremented values that make each table row unique. This is required by some database types.
     • Indicate that the table should be deleted after the macro or workflow has executed.
   • For Use an existing table, select the table to be used from the drop-down, and indicate whether the rows in the table should be deleted before the new data is written to the table.
3. On the Configuration tab , create joins and select the rows and columns:
   • Use the Table Mapping area to build relationships between matching records in different input tables. Refer to Table Mapping using joins for additional information.
   • Use the lower portion of the screen to designate the fields to be included in the output table, and to create a filter limiting the output records.
     • You can filter the Table Fields using the Search box to fields with matching strings. If you have multiple input tables, you can also filter the list by table using the drop-down list below the Search box.
     • To add Output Fields, drag them from Table Fields, or select them from the tables in the Table Mapping area.
     • For this action, you can also add a Add a computed field to the output and define an expression to determine its value.
     • To assign an alias to an output field after it has been added, click on the Edit icon next to the field and overwrite the name.
     • In the Filter area, build an expression to more narrowly define the records for which the Select action applies. Expressions can be created directly in the Filter area, or you can use the Expression Editor.
   • Click Preview to view the data or the SQL statement created from this action. Refer to Preview for information.
4. On the Mapping tab , map the output fields to an existing table structure when using an existing table as the output. Refer to Field Mapping for more information..
5. If the Performance tab is enabled in User Preferences, you can optionally change certain execution settings specific to this action. Refer to Change general preferences for additional information.

Select action data example

In the following example, data from selected columns in two tables is output as one record set, using the DC field for mapping purposes: