SQL editor

SQL editor is a tool to manage the queries you use to operate with managed tables. SQL editor is available for every step in SolveXia that contains SQL queries: Run Query in SQL table, Select from SQL table to Text/CSV, Select from SQL table to Access and Select from SQL table to Excel.

How to open SQL editor

1. Open the process.

2. Create or open an existing step that holds a SQL query.

3. Click on the box with the label Query.

4. The SQL editor will open in the side modal.

How to write a SQL query in the editor

SQL editor supports SQL language for querying tables in SolveXia. To write a query you should have at least Editor permissions to the process.

1. Open the SQL editor.

2. Type the simple SQL statement using one of your tables (if you don't have any tables in SolveXia learn how to create one).

You can use the snippet below. Change the name of the table from "Travel" to the one you want to query.

SELECT * FROM [Travel]

How to link a table and table columns to SQL query from the tables list

1. Open the SQL editor.

2. Find the table you want to link in the list on the left. You can use a filter input to search tables by names.

3. Hover the table and click on the link icon shown on the screenshot.

4. The table will appear in your query.

5. Expand the table to see the list of columns by clicking on the chevron icon next to the table name.

6. Hover the column name and click on the link icon next to its name.

How to link a table to SQL query using intellisense

1. Open the SQL editor.

2. Copy-paste the snippet into your editor.

SELECT * FROM 

3. Start typing the name of your table you want to link to the query.

4. Press enter or click on one of the items in the dropdown list. The table will appear in your query.

How to preview and export results of the query in SQL editor

You are able to preview queries that contain SELECT statements. Any statement meant to modify the data or structure of the table (SET, UPDATE, DELETE, etc) cannot be previewed.

The current SQL editor allows to preview maximum 50 rows of results for your SQL query.

If your SQL query is incorrect, on preview the editor will show an error message which will help you to fix the query. This can be helpful to debug SQL queries.

1. Write a SQL query in the editor and press Play button on the tool bar.

2. The result will be shown in the box below.

3. Tick Show system columns checkbox to see additional metadata about each row of the query results.

4. Click Export results as csv to download the file with the preview.

How to link a data step property to the SQL query

It is possible to link data step properties to SQL queries which brings more dynamics to your process. To do that:

1. Open SQL editor.

2. Click on the Link button in the tool bar to link one of your data step properties.

3. Choose the data step property in the modal and click Link button next to it.

4. The link will appear in your query highlighted in green.

How to view and read an execution plan for SQL query

The execution plan is a powerful tool that gives you insights about performance of your SQL queries. View and analyse your SQL statements execution plans to be able to optimise the performance of your processes and make them faster. To do that:

1. Open SQL editor.

2. Write a SQL query or use an existing one.

3. Click on the Execution plan button in the tool box.

4. The execution plan diagram will appear at the bottom box.

5. Hover one of the nodes to see information such as actual resource usage matrix and runtime warnings if any.

Basic SQL editor tools

Take a look at the basic UX features introduced in the SolveXia SQL editor:

1. Minimap (situated on the right of the text editor) is a condensed representation of the written query. Its purpose is to help the user orient through the code faster and with more ease. 

2. Find and replace functionality lets you search for a keyword in the query and replace with another keyword. To access the "find" tab, press "CTRL+F" and to access the "Find and Replace tab, press "CTRL+H" : 

Type in the keyword to search in the "Find" space and the item that it will be replaced with in the "Replace" space. Press "CTRL + ENTER" to apply the changes: 

3. Undo/redo lets you rollback latest changes or apply them back sequentially.

4. Zoom in/out if you want to change the resolution of your query editor.