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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s