Calculated Value In Where Clause

[WayneRyan]

In one of my queries, I'd like to reuse a calculated field as
criteria in the Where clause. SQL seems reluctant to want to
do this. You can RESTATE the calculation in the Where clause,
but can't refer to it by Name. You can Order by it, but it
can't be in the Where clause.

If the query result set consists on a set of Named columns, why
can you refer to a calculated column by Name in the "Order By"
clause but not in the Where clause?

Why is this OK?

Select FldA As FirstField, 
       FldB As SecondField,
       FldA - FldB As TheDifference  <-- Just a calculated field
From   SomeTable

And this is OK?

Select FldA As FirstField, 
       FldB As SecondField,
       FldA - FldB As TheDifference
From   SomeTable
Order By TheDifference               <-- Order By is OK

And this in not OK?

Select FldA As FirstField, 
       FldB As SecondField,
       FldA - FldB As TheDifference
From   SomeTable
Where TheDifference > 100            <-- Can't use TheDifference in Where clause

Don't want to repeat the calculation.

Select FldA As FirstField, 
       FldB As SecondField,
       FldA - FldB As TheDifference
From   SomeTable
Where  FldA - FldB > 100            <-- I know this works, but ...
Order By TheDifference

Wayne

 

[Pat Hartman]

Restate the calculation in the where clause.

 

[WayneRyan]

Thanks Pat,

I know that I can just restate the calculation in the Where clause, I was
just hoping to find out why.

Why can you use the Calculated column's Name in the Order By clause?
Why is that different?

Wayne

 

[Pat Hartman]

Why can you use the Calculated column's Name in the Order By clause?
Why is that different?

This appears to be an inconsistancy. You could report it as a bug to Microsoft.