 |
|
8000XXXX Errors Alerts ASP.NET 2.0 Classic ASP 1.0
Databases Access DB & ADO General SQL Server & Access Articles MySQL Other Articles Schema Tutorials Sql Server 2000 Sql Server 2005 General Concepts Search Engine Optimization (SEO)Search
ASP FAQ Tutorials :: Databases :: Other Articles :: How do I use a SELECT list alias in the WHERE or GROUP BY clause?
How do I use a SELECT list alias in the WHERE or GROUP BY clause?
Many people expect to be able to do this:
However, this results in the following:
The SELECT list is interpreted and evaluated last, so there is no way to use an alias defined there in other clauses of the statement. (One exception is the ORDER BY clause, which is not a T-SQL clause, but rather a cursor operator.)
Joe Celko says the following about the order of operations in a SELECT statement:
Of course, this can be a pain for more complex formulae, right? Thankfully, there are some workarounds. One is to use a derived table, e.g.
Another workaround is to create a view:
| SELECT FullName = LastName + ', ' + FirstName FROM Employees WHERE FullName = 'Bertrand, Aaron' |
However, this results in the following:
| Server: Msg 207, Level 16, State 3, Line 2 Invalid column name 'FullName'. |
The SELECT list is interpreted and evaluated last, so there is no way to use an alias defined there in other clauses of the statement. (One exception is the ORDER BY clause, which is not a T-SQL clause, but rather a cursor operator.)
Joe Celko says the following about the order of operations in a SELECT statement:
- Here is how a SELECT works in SQL ... at least in theory. Real products will optimize things when they can.
- Start in the FROM clause and build a working table from all of the joins, unions, intersections, and whatever other table constructors are there. The <table expression> AS <correlation name> option allows you give a name to this working table which you then have to use for the rest of the containing query.
- Go to the WHERE clause and remove rows that do not pass criteria; that is, that do not test to TRUE (reject UNKNOWN and FALSE). The WHERE clause is applied to the working in the FROM clause.
- Go to the optional GROUP BY clause, make groups and reduce each group to a single row, replacing the original working table with the new grouped table. The rows of a grouped table must be group characteristics: (1) a grouping column (2) a statistic about the group (i.e. aggregate functions) (3) a function or (4) an expression made up of the those three items.
- Go to the optional HAVING clause and apply it against the grouped working table; if there was no GROUP BY clause, treat the entire table as one group.
- Go to the SELECT clause and construct the expressions in the list. This means that the scalar subqueries, function calls and expressions in the SELECT are done after all the other clauses are done. The AS operator can give a name to expressions in the SELECT list, too. These new names come into existence all at once, but after the WHERE clause has been executed; you cannot use them in the SELECT list or the WHERE cluase for that reason.
- Nested query expressions follow the usual scoping rules you would expect from a block structured language like C, Pascal, Algol, etc. Namely, the innermost queries can reference columns and tables in the queries in which they are contained.
This means that a SELECT cannot have more columns than a GROUP BY; but it certainly can have fewer columns.
| SELECT FullName = LastName + ', ' + FirstName FROM Employees WHERE LastName + ', ' + FirstName = 'Bertrand, Aaron' |
Of course, this can be a pain for more complex formulae, right? Thankfully, there are some workarounds. One is to use a derived table, e.g.
| SELECT FullName FROM ( SELECT FullName = LastName + ', ' + FirstName FROM Employees ) sub WHERE sub.FullName = 'Bertrand, Aaron' |
Another workaround is to create a view:
| CREATE VIEW dbo.EmployeesConcat AS SELECT FullName = LastName + ', ' + FirstName FROM Employees GO -- then your SQL statement becomes a little easier to swallow: SELECT FullName FROM EmployeesConcat WHERE FullName = 'Bertrand, Aaron' |
Related Articles
How do I build a query with optional parameters? How do I calculate the median in a table? How do I create a store locator feature? How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns? How do I deal with multiple resultsets from a stored procedure? How do I debug my SQL statements? How do I determine if a column exists in a given table? How do I enable or disable connection pooling? How do I enumerate through the DSNs on a machine? How do I find a stored procedure containing <text>? How do I get a list of Access tables and their row counts? How do I get the latest version of the JET OLEDB drivers? How do I handle alphabetic paging? How do I handle BIT / BOOLEAN columns? How do I handle error checking in a stored procedure? How do I ignore common words in a search? How do I page through a recordset? How do I present one-to-many relationships in my ASP page? How do I prevent duplicates in a table? How do I prevent my ASP pages from waiting for backend activity? How do I prevent NULLs in my database from mucking up my HTML? How do I protect my Access database (MDB file)? How do I protect my stored procedure code? How do I protect myself against the W32.Slammer worm? How do I remove duplicates from a table? How do I rename a column? How do I retrieve a random record? How do I return row numbers with my query? How do I send a database query to a text file? How do I simulate an array inside a stored procedure? How do I solve 'Could not find installable ISAM' errors? How do I solve 'Operation must use an updateable query' errors? How do I temporarily disable a trigger? How do I use a variable in an ORDER BY clause? Should I index my database table(s), and if so, how? Should I store images in the database or the filesystem? Should I use a #temp table or a @table variable? Should I use a view, a stored procedure, or a user-defined function? Should I use recordset iteration, or GetRows(), or GetString()? What are all these dt_ stored procedures, and can I remove them? What are the limitations of MS Access? What are the limitations of MSDE? What are the valid styles for converting datetime to string? What datatype should I use for my character-based database columns? What datatype should I use for numeric columns? What does "ambiguous column name" mean? What is this 'Multiple-step OLE DB' error? What is wrong with 'SELECT *'? What naming convention should I use in my database? What should I choose for my primary key? What should my connection string look like? When should I use CreateObject to create my recordset objects? Where can I get this 'Books Online' documentation? Where do I get MSDE? Which database platform should I use for my ASP application? Which tool should I use: Enterprise Manager or Query Analyzer? Why are there gaps in my IDENTITY / AUTOINCREMENT column? Why can I not 'open a database created with a previous version...'? Why can't I access a database or text file on another server? Why can't I use the TOP keyword? Why do I get 'Argument data type text is invalid for argument [...]'? Why do I get 'Not enough space on temporary disk' errors? Why does ASP give me ActiveX errors when connecting to a database? Should I use COALESCE() or ISNULL()? Where can I get basic info about using stored procedures?