November 20, 2007 at 2:42 am
Can i use alias name in the where clause?
for ex:
select column1 as 'aaa' from table name
where aaa=
I tried but , its genearting sql error.
November 20, 2007 at 3:07 am
You can't refer to the columns by the alias name in a where clause but you refer it in an ORDER BY clause....
--Ramesh
November 21, 2007 at 1:06 pm
You can't directly. But you can set it up indirectly:
select aaa
from (
select column1 as 'aaa' from table name
)
where aaa = something;
Of course, all you're really accomplishing here is creating the ability to refer to the alias in the where clause. I generally don't use this for this purpose unless I have something like:
select col1, dbo.SomeFunc( col2 ) as v1, DateDiff( dd, col3, col4 ) as v2
from tablename
where dbo.SomeFunc( col2 ) = value1
and DateDiff( dd, col3, col4 ) > value2
it is a lot easier to write "where v1 = value1 and v2 = value2"
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
November 26, 2007 at 4:53 am
Many people expect to be able to do this:
SELECT
FullName = LastName + ', ' + FirstName
FROM Employees
WHERE FullName = 'Bertrand, Aaron'
However, this results in the following:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'FullName'.
The SELECT list is interpreted and evaluated last, so there is no way to use an alias defined there in other clauses of the statement. (One exception is the ORDER BY clause, which is not a T-SQL clause, but rather a cursor operator.)
Joe Celko says the following about the order of operations in a SELECT statement:
Here is how a SELECT works in SQL ... at least in theory. Real products will optimize things when they can.
Start in the FROM clause and build a working table from all of the joins, unions, intersections, and whatever other table constructors are there. The option allows you give a name to this working table which you then have to use for the rest of the containing query.
Go to the WHERE clause and remove rows that do not pass criteria; that is, that do not test to TRUE (reject UNKNOWN and FALSE). The WHERE clause is applied to the working in the FROM clause.
Go to the optional GROUP BY clause, make groups and reduce each group to a single row, replacing the original working table with the new grouped table. The rows of a grouped table must be group characteristics: (1) a grouping column (2) a statistic about the group (i.e. aggregate functions) (3) a function or (4) an expression made up of the those three items.
Go to the optional HAVING clause and apply it against the grouped working table; if there was no GROUP BY clause, treat the entire table as one group.
Go to the SELECT clause and construct the expressions in the list. This means that the scalar subqueries, function calls and expressions in the SELECT are done after all the other clauses are done. The AS operator can give a name to expressions in the SELECT list, too. These new names come into existence all at once, but after the WHERE clause has been executed; you cannot use them in the SELECT list or the WHERE cluase for that reason.
Nested query expressions follow the usual scoping rules you would expect from a block structured language like C, Pascal, Algol, etc. Namely, the innermost queries can reference columns and tables in the queries in which they are contained.
This means that a SELECT cannot have more columns than a GROUP BY; but it certainly can have fewer columns.
What we can take from all of this, is that you have to repeat your alias expression; so, the above query becomes:
SELECT
FullName = LastName + ', ' + FirstName
FROM Employees
WHERE LastName + ', ' + FirstName = 'Bertrand, Aaron'
Of course, this can be a pain for more complex formulae, right? Thankfully, there are some workarounds. One is to use a derived table, e.g.
SELECT FullName FROM
(
SELECT
FullName = LastName + ', ' + FirstName
FROM Employees
) sub
WHERE sub.FullName = 'Bertrand, Aaron'
Another workaround is to create a view:
CREATE VIEW dbo.EmployeesConcat
AS
SELECT
FullName = LastName + ', ' + FirstName
FROM Employees
GO
-- then your SQL statement becomes a little easier to swallow:
SELECT
FullName
FROM EmployeesConcat
WHERE FullName = 'Bertrand, Aaron'
karthik
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply