Contents x
    SQL Logic Step
    • 2 Minutes to read
    • Print
    • Dark
      Light
    • PDF
    Contents

    SQL Logic Step

    • 2 Minutes to read
    • Print
    • Dark
      Light
    • PDF
    Article Summary

    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.

    Note:
    • The SQL script is not encrypted in any way. Please avoid using credentials in the script.
    • The SQL script runs according to user configurations. Users are responsible for any changes to the table, schema, or data that occur due to the script.
    • Rivery's look has been updated, but it still serves the same functionality.

    How it Works

    1. To begin, select SQL / Script as the Logic Step type and the cloud database to which you want to connect.

    ezgif.com-gif-maker 1.gif

    1. 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.

    ezgif.com-gif-maker 7.gif

     

     

    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

    1. Select the 'Variables' tab in the upper right corner.
      image.png

    2. 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.

    image.png

    1. Click Apply Changes.

    image.png

    1. 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:
    ezgif.com-gif-maker 3.gif

    Results Tab

    The Result tab displays the output of your SQL script/query.
    Click Run to view the output:
    ezgif.com-gif-maker 4.gif

    Note:

    When the output reaches the maximum of 500 rows, a notification appears.

    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:

    image.png

    Move & Scroll

    To make things easier, the preview window may be moved and scrolled:
    ezgif.com-gif-maker 6.gif

    Download SQL

    The SQL query can be downloaded to your local computer.
    Click on the arrow pointing down in the upper right corner:
    ezgif.com-gif-maker 5.gif

    Was this article helpful?
    What's Next