Working with multiple named subqueries (CTE) in a query

Last modified:


To add your first named subquery in a CTE (WITH clause0, right-click on the Design Pane and select "Add Common Table Expression" - "Add New Common Table Expression". 

A new tab will be created for this named subquery. Also this subquery will be added to the current active subquery (main query on the screenshot above) and you'll see a new datasource on the Design Pane. You can right click on the tab to Rename or Delete a named subquery.

After switching to the newly created subquery tab, you can add another named subquery to CTE:

You can create recursive named subqueries, use one in another taking their order into account.

To review the structure of subqueries in a query, you can open the query structure tree.

In the upcoming version 3.7.6, this tree will be significantly improved, including the ability to perform various actions with query elements, including named sub-queries.

Currently, the visual interface does not allow you to define a list of output fields for named subqueries and re-order named subqueries within the WITH clause, but as far as Visual Query Builder has the ability to parse queries and represent them visually, you can perform these manipulations in the query text and get back to the visual way of building your query. We are constantly working on the improvement of visual UI, so these shortcomings will be addressed in the future.

 


Is this article helpful for you?