On 08/21/2017 11:51 PM, Stephen Chrzanowski wrote:
I think I may have found a query issue. I haven't checked the SQLite docs to see if this is something that is specified as an order of operations, or if this is a check that has been overlooked.
I think using a column alias in a complex expression within an ORDER BY is non-standard SQL. An extension that was accidentally implemented and is now maintained for backward compatibility. In SQL Server you can use a column alias as a standalone ORDER BY term but not as part of a complex expression.
More here: http://sqlite.org/src/info/f617ea3125e9c https://stackoverflow.com/questions/25763920/why-cant-i-refer-to-a-column-alias-in-the-order-by-using-case Dan.
The database SQL code is here: https://pastebin.com/raw/FukX4qEB select [main].[StateLabels].[LabelText] as [FromState],[StateLabels1].[LabelText] as [ToState] from [main].[StateLabels] inner join [main].[StateTables] on [main].[StateLabels].[LabelID] = [main].[StateTables].[FromState] inner join [main].[StateLabels] [StateLabels1] on [StateLabels1].[LabelID] = [main].[StateTables].[ToState] order by [FromState],[ToState] I get an ordered list that I'd expect. However, if I change the order by portion to order by upper([FromState]), upper([ToState]) I get an undefined order (Actually, it looks like it is ordering based on the StateLabels.LabelID -- And this is the clue) But then, if I go and change the order by to look like: order by upper([main].[StateLabels].[LabelText]), upper([StateLabels1].[LabelText]) I get the original order mentioned. It seems like the query analyzer is picking a real field name versus the one I defined with "As". My bad design, sure, I'll take credit for that, but FWIW, I would have expected an error to be thrown because I've got two types of fields named FromState and ToState. One being I've defined by using "As" and the other by an actual field name in StateTables. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
