Raw SQL Step
  • Updated on 17 May 2016
  • 3 minutes to read
  • Print
  • Dark
    Light

Raw SQL Step

  • Print
  • Dark
    Light

This tutorial demonstrates how to use Raw SQL Step in Decisions . This step requires the designer to be able to write and understand SQL queries. Raw SQL Step is an advanced step. It can be used in places where we need to get specific data using complex SQL statements.
Example:
In this example we are going to use Raw SQL Step in Decisions to get a number of units in stock by category and supplier continent in Northwind demo database.
This example assumes the Northwind database has already been integrated with the Portal. For your own purposes, you can use your default Decisions database or you can integrate with another database. For more information on integrating a database, see Integrating an External Database with a Flow .

2017-01-05_111514.png

We begin in the Designer Folder by clicking Create Flow on the Folder Actions Panel.
Create-Flow4.jpg

Then, we Name the Flow and click Create to proceed to the Flow Designer.
Raw-SQL.jpg

In the Flow Designer we add RAW SQL Step from the Integrations > My Integrations > Database > Advanced category.

2017-01-05_112034.png

Next, we are going to set up RAW SQL Step . First, in the Connection Settings we have several Connection Options:

  • Use Current Connection (to query Decisions tables)
  • Use Custom Connection*(to define a connection to the database)*
  • Use External Database (to use one of the connections we have our decisions integrated with)

Because we have our Northwind tables already integrated with decisions, we are going to Use External Database option.

2017-01-05_112206.png

Then, using External Database picker we select our Northwind connection and press Ok to continue.

2017-01-05_112344.png

Next, in Output Settings we are going to decide if our queryReturns Data. In our case we check Return Data checkbox. Then, we have several Return Data Options:

  • Select Type (if we have a defined type that matches our query output)
  • Data Rows (output will be represented as a collection of Rows with generic Columns)
  • CSV File (data represented in FileData format CSV File)
  • All Report Data (outputs Report Data type)
  • Excel File (data represented in FileData format Excel File)
  • PDF File (data represented in FileData format PDF File)

In this example we pick CSV File.

2017-01-05_122041.png

Then, in the Inputs settings we pick Text Merge.Plain Mapping type for SQL Command and open Text Editor.

2017-01-05_112657.png

In the Merge Text Editor we define our custom SQL query and click Ok to continue.

2017-01-05_124115.png

Next, on the Done outcome from the Raw SQL Step we add Show Form step from the Favorite Steps category.

2017-01-05_115534.png

In the resulting window we Name the Form and click Create to proceed to the Form Designer.

2017-01-05_115618.png

Our Form is designed as following… It has a CSV Editor component to display ourRaw SQL query result, and a Button to close the Form . When we finish designing our Form we can save and close Form Designer.

2017-01-05_115821.png

Back in the Flow Designer we connect Failed outcome from theRaw SQL Step and Button outcome from the Show Form step to the End Step in the Flow . Then, we Show Mapping Editor for the Form Step.

2017-01-05_120120.png

In the Mapping Editor we map Result outcome from Raw SQL Step to the CSVEditor input for our Form . Save and closeMapping Editor.

2017-01-05_122143.png

This completes our Flow . We can click Debug Flow link on the top Panel of the Flow Designer to test our Flow .

2017-01-05_124258.png

Our Form opens and CSV Editor displays our complex query results. We click Button on the Form to continue.

2017-01-05_124335.png

Our Flow executes with no errors.

2017-01-05_124408.png

Was this article helpful?