Raw SQL Step

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.

For this example our Decisions should be integrated with following Northwind tables:

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.

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.

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

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 Excel File.

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

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

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

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

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.

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.

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

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

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

Our Flow executes with no errors.

Additional Resources

• Creating Your First Flow
• Creating Your First Form Flow
• Using the Mapping Editor
• Flow Designer Wizard Overview
• Using the Flow Debugger