473,738 Members | 11,192 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Using IS NOT NULL on column result from subquery

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?

Sep 17 '05 #1
3 13357
ol*****@gmail.c om 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
Sep 17 '05 #2
(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****@sommarsk og.se

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

Sep 17 '05 #3
Thanks! Works like a charm :)

Sep 17 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
32352
by: Caroline | last post by:
I need to update a table by copying a column from another table (having the same structure, but on another database), from the record having the same primary key. 1 - What is the correct query? 2 - I tried copying them record by record, but the datatype is ntext, (it displays <long
1
5062
by: Prasad Karunakaran | last post by:
I am using the C# DirectoryEntry class to retrieve the Properties of an user object in the Active Directory. I need to get the First Name and Last Name as properties. I know it is not supported with the ADSI NT Provider and only supported in the LDAP Provider. So given an UserId (UID) how can I read the First Name and Last Name using LDAP Provider. If anybody can help me with a C# sample code it would of great help. Thanks in advance.
7
6990
by: Serge Rielau | last post by:
Hi all, Following Ian's passionate postings on problems with ALTOBJ and the alter table wizard in the control center I'll try to explain how to use ALTOBJ with this thread. I'm not going to get into the GUI because it is hard to describe in text. First of all what is the purpose of ALTOBJ()? This procedure was created mostly for ISVs who need to do produce change scripts to upgrade application from release to release, but it can also
0
3390
by: anonieko | last post by:
This approach I found very efficient and FAST when compared to the rowcount, or Subquery Approaches. This is before the advent of a ranking function from DB such as ROW_NUMBER() in SQL Server 2005 and the likes of it. So This one works with SQL2000 What do you think?
6
20873
by: plaster1 | last post by:
Been trying to come up with a query to filter-down my sample set into distinct records. For instance, lets say column1 is a sample set, column2 is the parameter, and column3 is a name and column4 is a type (lets also say there is a fifth column (id) that is an id). What I need is one record per type per sample only if type is given, if not, then return that record as well. I've used a subquery to get as close to the desired query is as...
3
33765
by: Manikandan | last post by:
Hi, I have table with three columns as below table name:exp No(int) name(char) refno(int) I have data as below No name refno 1 a 2 b 3 c
5
8034
by: Anne | last post by:
Hello! Here is the statement in question: --STATEMENT A SELECT * FROM dbo.myTable WHERE colX in (SELECT colX FROM dbo.sourceTable) The problem with Statement A is that 'colX' does not exist in 'dbo.sourceTable'. It does, however, certainly exist in 'dbo.myTable'. Breaking the statement down, we have:
0
5708
debasisdas
by: debasisdas | last post by:
Using Subqueries ================== The sub query is often referred to as a nested SELECT, Sub - SELECT, or inner SELECT statement. The sub query executes once before the main query. The result of the sub query is used by the main query (outer Query). You can place the sub query in a number of SQL clauses. WHERE clause
0
3065
prabirchoudhury
by: prabirchoudhury | last post by:
CRITERIA; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | CritCode | int(4) | NO | PRI | 0 | | | Description | varchar(150) | YES | | NULL | | | CritGroup | varchar(10) | YES | | NULL | | | Detail | varchar(30) | YES | | NULL | ...
0
8969
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9476
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9335
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9208
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8210
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4570
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3279
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2745
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2193
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.