Work with the SQL Editor

The SQL Editor allows you to create and test SQL statements before you run your TestCase. The SQL Editor also offers an auto-escape function. You don't have to escape special characters in your SQL statements.

The SQL Editor is only available if you have installed and enabled the Tosca BI AddIn in Tosca Commander.

If you use dynamic expressions in your SQL statements, the Translate function of the SQL Editor enables you to see the value that Tosca BI uses at runtime.

You can use the SQL Editor in TestCases created with the following modules:

• BI DB Expert Module

• TBox DB Expert Module

• Metadata comparison Module

• Row by Row Comparison Module

• Early Access | Load Data into Database Module

To enter and test your SQL statement via SQL Editor, follow the steps below:

1. Ensure that you have defined a connection to your datasource:

   • In TestCases created with the BI DB Expert Module, select one of your connection in the TestStepValue Connection.

   • In TestCases created with the TBox DB Expert Module, specify your connection in the Open Connection TestStepValues.

   • In TestCases created with the Metadata comparison Module, specify your connection in the TestStepValues Connection, DSN or Connection string.

   • In TestCases created with the Row by Row Comparison Module, specify your connection in the TestStepValues Source and Target.

   • In TestCases created with the Early Access | Load Data into Database Module, specify your connection in the TestStepValue Source.

2. Select the TestStep or TestStepValue in the project tree on the left and click on the SQL Editor tab.

3. Enter your SQL statement into the SQL Query field. The SQL Editor offers syntax highlighting for keywords, strings, quoted identifiers, and support comments.

4. If you are using dynamic expressions in your SQL statements, you can translate them. To do so, click on Translate.

Tosca BI translates these expressions directly in the SQL Editor window.

5. To test your SQL statement, click on Run SQL. The SQL Editor limits the number of rows displayed in the Results Table to a maximum of top 1000 rows.

6. If you are using the Row by Row Comparison Module to execute Reconciliation tests, you can switch between SQL statements from the TestStepValues Source and Target. To do so, click on Source or Target.