Raw SQL Step

Last Updated: 07/02/2018 Introduced in Version: 3.0

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

 

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

Then, we Name the Flow and click Create to proceed to the Flow Designer.

 

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

 

2017-01-05_112034

 

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

 

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

 

2017-01-05_112344

 

Next, in Output Settings we are going to decide if our query Returns 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

 

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

 

2017-01-05_112657

 

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

 

2017-01-05_124115

 

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

 

2017-01-05_115534

 

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

 

2017-01-05_115618

 

Our Form is designed as following… It has a CSV Editor component to display our Raw 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

 

Back in the Flow Designer we connect Failed outcome from the Raw 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

 

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

 

2017-01-05_122143

 

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

 

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

 

2017-01-05_124335

 

Our Flow executes with no errors.

 

2017-01-05_124408

Additional Resources