- 2 Minutes to read
- Print
- DarkLight
- PDF
SQL Logic Step
- 2 Minutes to read
- Print
- DarkLight
- PDF
Overview
The Logic Step of 'SQL / Script' enables for in-database transformations to be performed using a query or a script (the SQL syntax to be used is that of your cloud data warehouse).
A SQL script is a collection of two or more SQL statements, as opposed to a SQL query, which is a single statement. The goal of a SQL script is to combine similar statements in order to accomplish a more complicated edition in the database.
How it Works
- To begin, select SQL / Script as the Logic Step type and the cloud database to which you want to connect.
- In the Source section:
a. Select the Connection Name.
b. Enter your SQL script/query into the SQL field.
Note:
If you're using colons, make sure they're preceded by double backslashes, or else the River will fail.
Working With Variables
In SQL Logic step, users can leverage dynamic variables to establish connections between Rivers, automate tasks, and exchange data.
How To Use Logic River Variables
Select the 'Variables' tab in the upper right corner.
Add a Variable ('temp' is an example).
Please Note:
- To create an array, tick the 'Contains Multiple Values' box.
- If the item 'Clear Value On Start' is checked, the value of the variable will always be updated to the original value when a new River is formed, regardless of what happens to it during a River.
- Multiple variables can be defined.
- Variable Encryption is currently only availalbe in Python steps.
- Click Apply Changes.
- To use the variable(s), run this:
SELECT *
FROM {temp}
Please Note:
Make sure to run this code whenever you refer to a String:
SELECT *
WHERE my_string_field = '{Var_Name}'
Expand & Preview Results
To Expand & Preview Results click the diagonal arrows in the SQL field:
Results Tab
The Result tab displays the output of your SQL script/query. When the output reaches the maximum of 500 rows, a notification appears.
Click Run to view the output:
Compiled SQL Tab
A compilation is the process of optimizing the query execution plan.
When variables are used in the query, the real value will be displayed in the compiled SQL tab.
'Compiled SQL' can only be used for SQL queries, not SQL scripts.
Here's an example:
Move & Scroll
To make things easier, the preview window may be moved and scrolled:
Download SQL
The SQL query can be downloaded to your local computer.
Click on the arrow pointing down in the upper right corner: