Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bryce Covert
Starting Member
12 Posts |
 Posted - 2005-06-21 : 12:11:02
|
| Hi,Lets say I have a query that I select an extra column that does additional math on a row. For example,SELECT ..., DateDiff(yyyy, tblEmployee.DOB, @CutoffDate) as AgeFROM ...It really frustrates me that I couldn't then say, WHERE Age > 21Instead, I'll do something like this, which I just think is ridiculous.WHERE DateDiff(yyyy, tblEmployee.DOB, @CutoffDate) > 21So it'd have to do the processing twice, once for selecting the data and another time to do the filtering.Is there any way to access this extra in other parts of the query? This is a simple example, but there are several places where I have subqueries that must be copied and pasted. Not to mention that if I change the subquery, I have to remember to change the other subquery.Any way to do this? Thanks. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-06-21 : 12:23:50
|
select * from(SELECT ..., DateDiff(yyyy, tblEmployee.DOB, @CutoffDate) as AgeFROM ...) t1where t1.Age > 21Go with the flow & have fun! Else fight the flow  |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-06-21 : 12:24:02
|
EDIT: Sniped  But you know what, with out any other criteria, you'll scan the whole table. I think I would keep it in the predicate the way you don't like it.SELECT * FROM (SELECT ..., DateDiff(yyyy, tblEmployee.DOB, @CutoffDate) as AgeFROM ...) AS XXXWHERE Age > 21Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
Bryce Covert
Starting Member
12 Posts |
Posted - 2005-06-21 : 12:51:23
|
| Thanks all. That works. I think in this situation, cleanliness is valued over speed, so I'll stick with that :)Bryce |
|
|
|
|
|
|
|
|
Select with extra columns