In this article

1 Overview
- 1.1 Prerequisites
- 1.2 Limitation
- 1.3 Known Issues
2 Snap Input and Output
3 Snap Settings
4 Preventing SQL Injection
- 4.1 Method-1: Simple Substitutions
- 4.2 Method-2: Dynamic Substitutions
5 Troubleshooting
6 Examples
- 6.1 Downloads

Overview

Use this Snap to add a WHERE clause in your SQL query. The WHERE clause is used to create filters/conditions. The Snap can also be used an an equivalent of a HAVING clause in the context of aggregate operations. This Snap also allows you to preview the result of the output query. You can validate the modified query using this preview functionality.

Prerequisites

None.

Limitation

Known Issues

Snap Input and Output

Input/Output	Type of View	Number of Views	Examples of Upstream and Downstream Snaps	Description

Input/Output	Type of View	Number of Views	Examples of Upstream and Downstream Snaps	Description
Input	Document	Min: 1 Max: 1	ELT Select ELT Copy	The SQL query in which you want to add the WHERE clause.
Output	Document	Min: 1 Max: 1	ELT Insert-Select ELT Limit	The modified SQL query with the WHERE clause and the condition.

Snap Settings

SQL Functions and Expressions for ELT

You can use the SQL Expressions and Functions supported for ELT to define your Snap or Account settings with the Expression symbol = enabled, where available. This list is common to all target CDWs supported. You can also use other expressions/functions that your target CDW supports.

Parameter Name	Data Type	Description	Default Value	Example

Parameter Name

Data Type

Description

Default Value

Example

Label

String

ELT Filter

GRADE_RECORD

ELT Filter

String

Required. The condition for the WHERE clause. You can also use Pipeline parameters in this field to bind values. However, you must be careful to avoid SQL injection. See Preventing SQL Injection for details.

N/A

GRADE = 1

GENDER = 'MALE'

Get preview data

Check box

Not selected

Selected

Preventing SQL Injection

You can pass Pipeline parameters as values in an SQL expression; however, if you do not phrase the expression properly it can lead to the parameter's name being bound as a value in the database. This potentially incorrect information being inserted into the database is known as SQL injection. It is thus necessary to take precautions when including Pipeline parameters in your SQL expression to prevent SQL injection. Based upon the intended use of the Pipeline parameter, use one or both the following methods to prevent accidental SQL injection:

Method-1: Simple Substitutions

You can reference the Pipeline parameter directly with a JSON-path without enabling expressions.

For example, if you want to use the Pipeline parameter, name, which contains the value of a column in the ELT Filter field:

colname = _name

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 Filter field:

_columnname + “= _name”

The Snap evaluates the expression and also carries out path substitutions.

Here is how it works

The Snap pre-processes the query to extract any JSON-Paths and converts them to bound parameters. For example, consider the following query:

_columnname + “= _name”

The Snap converts this query into the following before turning it into a prepared statement for the database:

colname = ?

The Snap evaluates the JSON-Path to get the value to bind the Pipeline parameter in the prepared statement.

Using escape characters

When expressions are disabled, use \ as an escape character to treat underscore (_) as a string.

For example:

colname = \_name

Troubleshooting

Error	Reason	Resolution

Error

Reason

Resolution

Syntax error when database/schema/table name contains a hyphen (-) such as in default.schema-1.order-details.

(CDW: Azure Synapse)

Azure Synapse expects any object name containing hyphens to be enclosed between double quotes as in "<object-name>".

Ensure that you use double quotes for every object name that contains a hyphen when your target database is Azure Synapse. For example: default."schema-1"."order-details".