Today I found a problem with alias name during retrieving values from database table with alias name. My query was like as following:  

========================================================
Select
EmpID,
Case EmpNameOrEmpFirstLastName
When ‘EN’ Then
EmpName
Else
EmpLast + ‘, ’ + EmpFirst
End As EmpName,
City
From tblEmployee
Where EmpName LIKE ‘N%’
=======================================================

When I execute this query I get all the record which contains the N% in EmpName as well as from FirstName. As you know that should not because I give alias name EmpName to EmpLast + ‘, ’ + EmpFirst. But here the problem is we cannot use the alias name in Where condition.

I resolve this problem by using #temp table like the following:
=======================================================
Select
EmpID,
Case EmpNameOrEmpFirstLastName
When ‘EN’ Then
EmpName
Else
EmpLast + ‘, ’ + EmpFirst
End As EmpName,
City
Into #tmpEmployee
From tblEmployee

Select * from #tmpEmployee where EmpName LIKE ‘N%’
=======================================================

It really very simple, isn’t it? You can use the reference link:
Reference: http://www.thescripts.com/forum/thread82710.html

So friends enjoy, we are the Sql Master:)
Regards,
Vijay Modi