In this article
...
Use this Snap to add a JOIN clause to join tables in separate queries coming from the upstream Snaps. This Snap also allows you to preview the result of the output query. You can validate the modified query using this preview functionality.
Note | ||
---|---|---|
| ||
Starting from 4.24 GA, ensure to configure an account for this Snap. |
Prerequisites
None.
Limitations and Known Issues
...
Parameter Name | Data Type | Description | Default Value | Example | ||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
Label | String |
| ELT Join | Combined Dataset | ||||||||
ELT Join Type | String/Drop-down list | Choose the join type to use in the SQL. Available options are:
See Snowflake Join Types for details. See Redshift Join Types for details. See Azure Synapse Join Types for details.
| Inner | Left outer | ||||||||
ELT Join Condition | String/Expression | Specify the condition to initiate the JOIN operation. If you do not specify any condition here, the Snap uses You can specify any SQL expression that has a boolean output (true or false).
| N/A | CUST_CODE1 = CUST_CODE | ||||||||
Left Table Alias | String/Expression | Specify the alias to use for the table in the first input view. This enables you to qualify the columns to join with an alias name and resolve any ambiguity due to identical column names. | N/A | TBL1 | ||||||||
Right Table Alias | String/Expression | Specify the alias to use for the table in the second input view. This enables you to qualify the columns to join with an alias name and resolve any ambiguity due to identical column names. | N/A | TBL2 | ||||||||
Resultant Column Names Prefix Type | Drop-down list | Choose an option from the list to prefix the resultant columns names with a table alias; this enables the Snap to prevent collision of identical column names in resultant table. Available options are:
| None | RT.D_DATE_SK | ||||||||
Get preview data | Check box |
| Not selected | Selected |
...
Method-2: Dynamic Substitutions
You must enable expressions when using Pipeline parameters for dynamic substitutions. Format the SQL expression, except the Pipeline parameter's reference, as a string.
For example, if you want to use the Pipeline parameter, name, which contains the value of a column in the ELT Join Condition field:
...
We need a query that contains a JOIN clause. This example demonstrates how we can use the ELT Join Snap to build a query with the JOIN clause.
First, we build SELECT queries to read the target tables. To do so, we can use two ELT Select Snaps, in this example: Read Part A and Read Part B. Each of these Snaps is configured to output a SELECT * query to read the target table in the database. Additionally, these Snaps are also configured to show a preview of the SELECT query's execution as shown:
Read Part A Configuration | Read Part B Configuration |
---|---|
...