Use Alias Name in Select And Where Clause


In this article I try to explain how to use Alias in "Select" and "Where" clause.

Hai Friend,

In Sql server 2005, normally we cannot use "Alias" names for futher calculations and in "Where" clause.


Eg: Select EmpCode, BasicSalary,
(Select Sum(Amount) from Remuneration Where EmpCode = A.EmpCode And Flg = 'A') as [Allowance],
(Select Sum(Amount) from Remuneration Where EmpCode = A.EmpCode And Flg = 'D') as [Deduction],
((BasicSalary + [Allowance]) - [Deduction]) as Salary
From SalaryDetails as A Where EmpCode = 'Emp0001'


If we use like the above query in SQL server will display an error message
Invalid column name 'Allowance'.


Select Temp.EmpCode ,Temp.BasicSalary, Temp.[Allowance], Temp.[Deduction], ((Temp.Salary + Temp.[Allowance]) - Temp.[Deduction]) as Salary
From
(Select EmpCode, BasicSalary,
(Select Sum(Amount) from Remuneration Where EmpCode = A.EmpCode And Flg = 'A') as [Allowance],
(Select Sum(Amount) from Remuneration Where EmpCode = A.EmpCode And Flg = 'D') as [Deduction]
From SalaryDetails as A Where EmpCode = 'Emp0001') as Temp


The above code allow the user to use "Alias" for futher operations in the same query.

Friends this is my first attempt to post a code snippets. If there is anything wrong just show me.

Thanks & Regards
Bobbin Paulose


Related Articles

More articles: SQL Alias SQL query SQL alias clause

Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: