Sql Server + alias name in where condition

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

Advertisements

About Vijay Modi

Having 12+ years of experience in web application development. Expertise in various domains like E-Commerce, E-Learning, Insurance. I have expertise in web application development, window application development, Performance improvement, bug fixing etc. I am believing in quality work and achieving deadlines. Also like to work on new technologies and quick learner.
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

2 Responses to Sql Server + alias name in where condition

  1. Dhimant Trivedi says:

    I don’t quite understand your purpose behind creating the temp table and then searching it. You could’ve directly used

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

    • riteshshahh says:

      no need to go for the “Case” in where neither need Temporary table. we can simply do it like:

      select * from
      (
      Select
      EmpID,
      Case EmpNameOrEmpFirstLastName
      When ‘EN’ Then
      EmpName
      Else
      EmpLast + ‘, ‘ + EmpFirst
      End As EmpName,
      City
      From tblEmployee
      ) as t Where EmpName LIKE ‘N%’

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s