Using column created on the fly in a WHERE clause 2

[LindaH]

(SQL Server 2000)

I'm setting up a column on the fly and giving it an alias name. I want to use the alias in the WHERE clause, but it's not working. My code: [ul]_{SELECT ProfileID, SUBSTRING(Fname,1,2) As "FnameCmpr"
FROM Profile
WHERE SSN = '123456789'
AND Lname = 'Doe'
AND FnameCmpr = 'Jo'}[/ul] What I'm basically trying to do is compare the first two letters of the first name in the database (Fname) against a value I'm passing in. In the code above, the value 'passed in' is 'Jo'.

 

[bperry]

I don't think you can use the Alias name in the Where clause, (although you can in the Order By clause).

One thing you can so is repeat your extraction:
....AND FnameCmpr = SUBSTRING(Fname,1,2)

Another thing that's useful at times is to wrap your query into a derived table:

Select ProfileID, FNameCmpr
from
(
SELECT ProfileID,SUBSTRING(Fname,1,2) As "FnameCmpr"
FROM Profile
WHERE SSN = '123456789'
AND Lname = 'Doe'
) as dt
WHERE FnameCmpr = 'Jo'
------------------------
Pick your poison. I prefer the first option.

 

[SQLBill]

From my understanding, you can't use an alias in a WHERE clause because it doesnt' exist yet. Let me break it down....

You want to create (select) two columns, one with an alias
but before you create (select) that information you want to limit (where) what you retrieve to put in those two columns.

So, until the information is actually found, the select hasn't happened and if the select hasn't happened yet, the alias hasn't been assigned.

Again, this is based on my understanding of how the query works. I could be wrong.

-SQLBill

 

[bperry]

I like SQLBill's analysis, and don't disagree with it. But that doesn't make it any less annoying.

(1) If that train of thought was followed consistently, it might be more acceptable. But since the alias IS available to use in the Order By clause, then why not the Where clause too? (Or the Group By, for that matter?)

(2) I've worked in other programming languages where the calculated columns are indeed available to filter on. (I think it's mostly just how you construct your interpreter engine.)

(3) It creates a maintenance headache to code your calculation in two places, as LindaH is probably going to do.


All in all, allowing the alias in the Where/Group BY clauses would be on my wish list for changes in Sql (either in the Ansi standard or as a t-sql extension.) Perhaps it is in ANSI99....anybody know?

bp

 

[LindaH]

I'm not sure where you mean to code the repeat extraction. My code wouldn't look like this, would it? Where do I filter on 'Jo?'

SELECT ProfileID, SUBSTRING(Fname,1,2) As "FnameCmpr"
FROM Profile
WHERE SSN = '123456789'
AND Lname = 'Doe'
AND FnameCmpr = SUBSTRING(Fname,1,2)

Thanks for the input.

Linda

 

[bperry]

Yes, my apologies, I mislead you in my earlier post.

I should have typed:

SELECT ProfileID, SUBSTRING(Fname,1,2) As "FnameCmpr"
FROM Profile
WHERE SSN = '123456789'
AND Lname = 'Doe'
AND SUBSTRING(Fname,1,2) = 'Jo'