Automatically Expand Named Expressions into SQL statement
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
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.
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.