By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Using an alises in the WHERE clause

P: 21
Hi,

I'm working in a SQL Server 2008 enviroment trying to get the code below to work.

The error I get is "Invalid column name 'PHONE'."

Thanks in advance for your help.

SELECT DISTINCT TOP 5 REPLACE(HOME_PHONE,'-','') AS PHONE
FROM X
WHERE HOME_PHONE IS NOT NULL AND DO_NOT_CALL IN ('Y','X','1') AND PHONE NOT IN
(SELECT DISTINCT PHONENUMBER FROM X WHERE PHONENUMBER IS NOT NULL)
Jun 25 '09 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
On your query, do you have the PHONE column in your table x?

SELECT DISTINCT TOP 5 REPLACE(HOME_PHONE,'-','') AS PHONE
FROM X
WHERE HOME_PHONE IS NOT NULL AND DO_NOT_CALL IN ('Y','X','1') AND PHONE NOT IN
(SELECT DISTINCT PHONENUMBER FROM X WHERE PHONENUMBER IS NOT NULL)

Happy Coding!

--- CK
Jun 25 '09 #2

P: 21
No. HOME_PHONE is the field. PHONE is the alises I'm using in the SELECT statement.

I can explain alittle more. The reason for the REPLACE function is that in the 1st table some of the phone numbers have -'s in them where in the 2nd table (in my sub-query) they do not.
Jun 25 '09 #3

P: 21
Found a work-around the alises to fix this issue.

SELECT DISTINCT REPLACE(HOME_PHONE,'-','')
FROM X
WHERE HOME_PHONE IS NOT NULL AND DO_NOT_CALL IN ('Y','X','1') AND REPLACE(HOME_PHONE,'-','') NOT IN
(SELECT DISTINCT PHONENUMBER FROM X WHERE PHONENUMBER IS NOT NULL)
Jun 25 '09 #4

P: 10
For aliases in WHERE clause you can use two level select query.

SELECT * FROM (
SELECT DISTINCT REPLACE(HOME_PHONE,'-','') a
FROM X )
WHERE HOME_PHONE IS NOT NULL AND DO_NOT_CALL IN ('Y','X','1')
AND a NOT IN
(SELECT DISTINCT PHONENUMBER FROM X WHERE PHONENUMBER IS NOT NULL)


Hope this will help you .

-Pankaj Tambe.
Jul 17 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.