Active Query Builder support area

Automatically Expand Named Expressions into SQL statement

Andrew Clark

9 years ago
updated 8 years ago
Completed

Maybe there is already a property to do this, but I could not find anything so I apologize if it is already there.

The problem is that some SQL syntaxes allow "relaxed" rules for referencing named expressions in the SELECT clause. For instance if I add an expression like:

ShippedDate - OrderDate As DaysWaiting

Then I can reference that same expression later in the same SELECT clause e.g:

Round(DaysWaiting/7) As WeeksWaiting

I realize that is not "standard" SQL and that you can get around the problem for instance by using a CTE but that is fairly complicated for a basic user to implement and to understand why it is not allowed. The problem is complicated by the fact that the same expression (e.g. WeeksWaiting) could be referenced in multiple places: the SELECT clause, the WHERE clause, the ORDER BY clause, etc.

It would be really, really nice if the ActiveQueryBuilder took care of this automatically, for instance if these two fields were in the Columns Pane:

Expression: ShippedDate - OrderDate
Alias: DaysWaiting

Expression:Round(DaysWaiting/7)
Alias: WeeksWaiting

Then, when the SQL statement was created the expression would be automatically expanded e.g.:

Select ShippedDate - OrderDate As DaysWaiting, Round((ShippedDate - OrderDate)/7) as WeeksWaiting.

In fact it becomes almost impossible to change an expression "after the fact" without this support, for instance imagine a calculation that derives margins: if that calculation changes because of new business rules (for instance a different tax%) then every other expression that references the new business rule (which could appear multiple times in multiple SQL clauses) would have to be changed to reflect that new value.

You would probably have to add support for the SQL parser to "look up" each expression to see if there was an (exact) match to a named expression, so it is certainly not trivial but I know that other products (for instance ShowCase) do something similar. Maybe you could add some "decoration" around named expressions for instance double-parentheses?? e.g. ((ShippedDate - OrderDate)) so that ActiveQueryBuilder would "know" that the those referenced a named expression?

Really AQB is already outstanding, this is probably the last thing that I can think of for basic SQL support that would really be useful; thank you again for the support that you've already added.

Replies 5

Oldest first
Newest first Oldest first

Andrey Zavyalov, PM

9 years ago

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.

Reply Link

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.

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.

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.

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.