|
|
Posted by Erland Sommarskog on 09/17/05 15:21
(olanorm@gmail.com) writes:
> I have a query where one or more of the columns returned is a result
> from a subquery. These columns get their own alias. I want to filter
> out the rows containing NULL from the subqueries but it just won't
> work. When running columnAlias IS NOT NULL i just get the error
> "Invalid column name 'columnAlias'.
>
> This is the query:
>
> SELECT k.UserId, k.Lastname, k.Firstname,
> (SELECT kscr.answer FROM Results kscr WHERE kscr.UserID =
> k.UserID) AS myColumnAlias
> FROM Users k
> WHERE myColumnAlias IS NOT NULL)
As Simon said, you cannot use a column alias in this way. The way to this
is to use a derived table:
SELECT UserId, Lastname, Firstname, myColumnAlias
FROM (SELECT k.UserId, k.Lastname, k.Firstname,
(SELECT kscr.answer FROM Results kscr WHERE kscr.UserID =
k.UserID) AS myColumnAlias
FROM Users k) AS x
WHERE myColumnAlias IS NOT NULL
A derived table is a "temp table within the query", but it is never
materialized and the optimizer may recast the computation order, as
long as this does not affect the query. This is a very powerful tool.
The query proposed by Simon is probably better for this task though.
Although, his use of an outer join makes it look more complicated
that it has to be. This should do as well:
select k.UserID, k.LastName, k.FirstName,
kscr.Answer as 'myColumnAlias'
from dbo.Users k
join dbo.Results kscr on k.UserID = kscr.UserID
where kscr.Answer is not null
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|