No Aliases in Where Clauses

I was bit by the ‘No Aliases allowed in Where Clauses’.  It also explained Linq-to-SQL’s seemingly strange syntax at the same time.

The SQL Order of Operations isn’t as its written by developers, but the other way around.

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause

 

So, when you have an aliased column, it can’t use that in the WHERE Clause because it hasn’t been told what that means yet.  So what happens when you want to use the Aliased column in the Where Clause?

There’s a Hack you can use:

Wrap the entire statement (or that line, as applicable) with a SELECT statement that contains the WHERE Clause you want to alias.

For instance:

select * from ( select a + b as aliased_column from table ) dt where dt.aliased_column = whateveryouputhere.

It does it rather nicely. You can find the question and answer (not mine) on Stack Overflow.

 

Leave a Reply