Active Query Builder support area

the alternative syntax of select: with ALIAS1 as (select ...), ALIAS2 as (select ...),... select ... from ALIAS1, ... ALIAS2

Avatar
  • updated
  • Completed

Hello,

"with <common_table_expression>" is an alternate syntax that can be useful as select output. This syntax can work recursive or not based on the SQL dialect.

Here is a link: https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017 

Is it possible to obtain that? 

Avatar
Andrey Zavyalov, PM

Hello, 

Yes, it is possible. You must use MSSQLSyntaxProvider to obtain that.

queryBuilder1.SyntaxProvider = new MSSQLSyntaxProvider();

More details here: What are the Syntax and Metadata providers for?

Avatar
Daniel Zvinca

My link was just an example for mssql. Would be nice to have this for other dialects (firebird also has it) as an alternative to standard select. Subqueries are available already in interface. With alias as select syntax has a few advantages over classic select: 1. it creates sort of adhoc views that can be used in other adhoc views, 2. allows recursivity (where possible), 3. (my personal opinion) it has a simplified structure

Avatar
Andrey Zavyalov, PM

All syntax providers should have support for respective database servers' SQL syntax. If you find any problems, please let me know.

Only a few clauses are currently not supported: PIVOT/UNPIVOT, CROSS/INNER APPLY, NATURAL JOIN, VALUES.

Avatar
Daniel Zvinca

both these simple queries are equivalent in Mssql, Firebird, MonetDB:

1. select a, b, c from Table T where x=1

2. with T as (select a, b, c from Table where x=1 )  select * from T

How can I generate 2nd syntax using AQB? 

(above examples are trivial, there are some other complex queries that would benefit from 2nd form, that otherwise are not possible in 1st form)

Avatar
Daniel Zvinca

Correction, even if 1 was possible, the correct form is: 

 1. select T.a, T.b, T.c from Table T where T.x=1

Avatar
Andrey Zavyalov, PM

I will illustrate this with the ASP.NET Edition, but it is the same for all editions.

1. Add a named subquery to CTE:

2. Switch to this subquery.

3. Do the following on the subquery canvas:

4. You will see the following diagram on the main query canvas:

Avatar
Daniel Zvinca

Perfect, is exactly the answer I was looking for! Thank you!