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

Answered

Comments

7 comments

  • Official comment
    Avatar
    Sergey Smagin (Product manager)

    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?

    Comment actions Permalink
  • 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

    0
    Comment actions Permalink
  • Avatar
    Sergey Smagin (Product manager)

    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.

    0
    Comment actions Permalink
  • 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)

    0
    Comment actions Permalink
  • 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

    0
    Comment actions Permalink
  • Avatar
    Sergey Smagin (Product manager)

    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:

    0
    Comment actions Permalink
  • Avatar
    Daniel Zvinca

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

    0
    Comment actions Permalink

Please sign in to leave a comment.