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
