Automatically Expand Named Expressions into SQL statement

Planned

Comments

5 comments

  • Hello, Andrew.

    There's no properties to allow such behavior.

    Could you please let us know what are these SQL syntaxes that support such feature. Of course we will consider it for implementation if this feature presents in some of the popular database servers.

    0
    Comment actions Permalink
  • Avatar
    Andrew Clark

    For instance, MySQL allows aliases in the GROUP BY and HAVING clauses (which I don't think "standard" SQL supports).

    http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

    It looks like AQB already supports aliases in the ORDER BY clause (which standard SQL does support, if I understand correctly) for instance if I type directly into the SQL statement:

    ...ORDER BY WeeksWaiting

    ...switch back to AQB and the Sort Type column next to WeeksWaiting is correctly set, then switch back to the SQL statement again the expression is automatically expanded:

    ...Order By
    ROUND((ShippedDate - OrderDate) / 7)

    ...so the basic support looks like it is already there? If that works, maybe it wouldn't be hard to extend that support to SELECT and WHERE?


    There are lots of questions that people have asked about this exact support, just simply Google:

    alias where clause

    And the first several questions are people asking how to reference an ALIAS in the WHERE clause:

    http://stackoverflow.com/questions/356675/using-an-alias-in-a-where-clause
    http://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause

    ...etc. so it's obviously confusing to a lot of people.

    The ShowCase product http://www.helpsystems.com/showcase does the expression expansion automatically (it's not a major product, admittedly).

    Again, I understand that this is not standard SQL and this wouldn't necessarily be the default (i.e. there would need to be a property to control it) but this is something that users are always confused about when they build expressions, and AQB could certainly make it much less confusing by adding that support. Just something to consider possibly adding in a future release, thank you again.

    0
    Comment actions Permalink
  • We must allow this feature only for those servers which have support of this feature. If none of the supported by Active Query Builder servers allow this, then it doesn't seem to be a good idea for me.

    In fact, we can add this feature but expand these aliases to expressions (or derived tables) in result SQL as we do this with virtual fields and alternate names. May be it's a good point for our future development, so thank you for your suggestion.

    0
    Comment actions Permalink
  • Avatar
    Andrew Clark

    Microsoft Access (I assume all Jet connections) already allow an alias to be referenced in the SELECT clause, for instance this runs against Northwind in MS Access (where WeeksWaiting references a previously calculated alias DaysWaiting):

    Select
    Customers.CustomerID,
    Customers.CompanyName,
    DateDiff('d', Orders.OrderDate, Orders.ShippedDate) As DaysWaiting,
    Round(DaysWaiting / 7, 0) As WeeksWaiting
    From
    Customers Customers Inner Join
    Orders On Customers.CustomerID = Orders.CustomerID


    The (almost) exact same query run against identical files (Northwind) in SQL Server does not work (the only difference is the first parameter is unquoted to the DateDiff() function):

    Select
    Customers.CustomerID,
    Customers.CompanyName,
    DateDiff(d, Orders.OrderDate, Orders.ShippedDate) As DaysWaiting,
    Round(DaysWaiting / 7, 0) As WeeksWaiting
    From
    Customers Customers Inner Join
    Orders On Customers.CustomerID = Orders.CustomerID


    Error:"Invalid Column Name 'Days Waiting'"


    Again, it's these kinds of limits that make it very confusing to the user as to what is "correct". (MS Access does not, however, allow aliases to be used in the WHERE clause).

    Maybe your suggestion to use virtual fields and alternate names is the "correct" solution. For now we have to add conditional logic to our expression editor to allow for the different syntaxes (i.e. inserting the entire expression for SQL server or just inserting the Alias name for MS Access) which is cumbersome, but I'm not sure what the best solution is at this point - thank you again.

    0
    Comment actions Permalink
  • Hello, Andrew.

    Thank you for your suggestion and detailed explanation. The feature you requesting is quite interesting, but it takes much time to implement, so we have to postpone it. Hopefully we'll get back to it in the future.

    0
    Comment actions Permalink

Please sign in to leave a comment.