Using IS NOT NULL on column result from subquery

[olanorm]

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)

When running without the WHERE clause, I get the following results:

UserId Lastname Firstname myColumnAlias
113 Norman Ola jepps
820 Karlsen Kjell
830 Pens Jens juubidoo

What I want is to get rid of UserId=820. What am I doing wrong?

[Simon Hayes]

ol*****@gmail.com wrote:

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)

When running without the WHERE clause, I get the following results:

UserId Lastname Firstname myColumnAlias
113 Norman Ola jepps
820 Karlsen Kjell
830 Pens Jens juubidoo

What I want is to get rid of UserId=820. What am I doing wrong?

You can only reference a column alias in the ORDER BY clause, not in the
WHERE clause. You could repeat the whole subquery in the WHERE clause,
but using an outer join is probably easier:

select
k.UserID,
k.LastName,
k.FirstName,
kscr.Answer as 'myColumnAlias'
from
dbo.Users k
left outer join dbo.Results kscr
on k.UserID = kscr.UserID
where
kscr.Answer is not null

Simon

[Erland Sommarskog]

(ol*****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[olanorm]

Thanks! Works like a charm :)