Get Rows with Join
Use the Get Rows with Join action to retrieve rows by combining columns from one (self-join) or more tables based on matching SQL join clause.
The Get Rows with Join action does not support the following types of joins:
• Natural join
• Cross join
 |
Equi join is supported on the join condition by using an equality operator with inner join.
|
Complete the following steps to use the Get Rows with Join action in your workflow:
1. Drag the Get Rows with Join action under SQL Connector to the canvas, place the pointer on the action, and then click 
or double-click the action. The Get Rows with Join window opens.
or double-click the action. The Get Rows with Join window opens.2. Edit the Label, if needed. By default, the label name is the same as the action name.
3. To add an SQL connector type, refer to Supported SQL Connector Types.
If you previously added a connector type, select the appropriate Connector Type, and under Connector Name, select the connector.
4. Click TEST to validate the connector.
5. Click MAP CONNECTOR to execute the action using a connector that is different from the one that you are using to populate the input fields. In the Runtime Connector field, provide a valid SQL connector name. For more information about MAP CONNECTOR, see Using Map Connector.
6. Under the Table group, do the following:
a. In the Table list, select the appropriate database table or view.
b. In the Alias field, enter an alias for the table. The alias is used to identify the table. This step is optional.
| | You must select a minimum of 2 tables. To add more than 2 tables, click Add. Click  to delete any tables that you added. |
7. In the Limit field, enter the number of rows that you want to retrieve. You can retrieve a maximum of 5000 rows at a time.
8. In the Skip field, enter the number of top rows that you want to skip from the result.
9. In the Join Clause Using list, do one of the following:
If you chose Selection in the Join Clause Using list | ||
|---|---|---|
a. Under the Join group, in the Left Table list, select the table name or alias. b. Under the Join Conditions group, do the following: a. In the Join Type list, select one of the following options: ▪ Inner Join ▪ Left Outer Join ▪ Full Outer Join ▪ Right Outer Join
b. In the Right Table list, select the table name or alias. c. In the Left Column list, select the column that you want on the left for the join condition. In case of mapping, append the column name with the table name. For example, tablename.columnname. If you have specified the alias, append the column name with the alias. For example: alias.columnname. d. In the Operator list, select the appropriate conditional operator. e. In the Right Column list, select the column that you want on the right for the join condition. In case of mapping, append the column name with the table name/alias. For example, tablename.columnname. If you have specified the alias, append the column name with the alias. For example: alias.columnname. Click Add to add multiple join conditions. Click to delete any join condition that you added. |
OR
If you chose Query in the Join Clause Using list |
|---|
In the Join Clause field, enter all required inputs in either of the following SQL Query formats: • table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name • table1 alias1 FULL OUTER JOIN table2 alias2 ON alias1.column_name = alias2.column_name |
10. Select Form to provide inputs for individual input fields listed in the form, or select Assign JSON to specify a single JSON with all required inputs.
If you chose Form |
|---|
a. Under the Select Columns group, select the Distinct check box to return unique values in the column. This is optional. b. Under the Columns group, click Add, and in the Column list, select the column that you want to display in the result. In case of mapping, append the column name with the table name/alias. For example, tablename.columnname. If you have specified the alias, append the column name with the alias. For example: alias.columnname. Click Add to add multiple columns. Click to delete any column that you added.c. Under the Where group, in the Join Clauses by list, select one of the following options: ◦ AND—All conditions specified under the Attributes group must return true. ◦ OR—Either of the conditions specified under the Attributes group must return true. d. Under the Attributes group, click Add, and do the following: a. In the Attribute list, select the column that you want to filter. In case of mapping, append the column name with the table name/alias. For example, tablename.columnname. If you have specified the alias, append the column name with the alias. For example: alias.columnname. b. In the Operator list, select the appropriate conditional operator. c. In the Value field, enter the value of the column for the filter. Click Add to add multiple attributes to the join clause. Click to delete any attribute that you added.If you add the same attributes and select AND in the Join Clauses by list, the attributes are joined by the OR operator. |
OR
If you chose Assign JSON |
|---|
In the Select Columns field, enter all required inputs in the {"distinct":Boolean Value, "columns":[{"columns":"string"}]} format. For example, you can specify the following values in this field: • {"distinct":true/false, "columns":[{"columns":"tablename.columnname"}]} • {"distinct":true/false, "columns":[{"columns":"alias.columnname"}]} |
| | If you do not select the column that has a primary key and do not select the Distinct check box, the column with the primary key is still returned in the output schema. |
11. In the Where Clause Using list, do one of the following:
If you chose Selection in the Where Clause Using list |
|---|
a. Under the Where group, in the Join Clauses by list, select one of the following options: ◦ AND—All conditions specified under the Attributes group must return true. ◦ OR—Either of the conditions specified under the Attributes group must return true. b. Under the Attributes group, click Add, and do the following: a. In the Attribute list, select the column that you want to filter. b. In the Operator list, select the appropriate conditional operator. c. In the Value field, enter the value of the column for the filter. Click Add to add multiple attributes to the join clause. Click to delete attributes.If you add the same attributes and select AND in the Join Clauses by list, the attributes are joined by the OR operator. |
OR
If you chose Query in the Where Clause Using list |
|---|
In the Where Clause field, enter all required inputs in either of the following SQL Query formats: • table1.column1 = 'text value' AND table2.column2 >= 13 • alias1.column1 = 'text value' AND alias2.column2 >= 13 |
12. Under the Order group, click Add, and do the following:
a. In the Column Name list, select the column by which you want to order the result.
In case of mapping, append the column name with the table name/alias. For example, tablename.columnname. If you have specified the alias, append the column name with the alias. For example: alias.columnname.
b. In the Order list, select ASCENDING or DESCENDING.
Click Add to add multiple columns to order the results. Click to delete the columns that you added to order.
to delete the columns that you added to order.13. Click Done.
Output schema
The output schema for the Get Rows with Join action returns multiple rows.
If you specified an alias for the table, then the output schema returns the rows in the alias.columnname format. If you do not specify an alias for the table, then the output schema returns the rows in the tablename.columnname format.
