Using an alises in the WHERE clause

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)

re: Using an alises in the WHERE clause

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

re: Using an alises in the WHERE clause

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.

re: Using an alises in the WHERE clause

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)

re: Using an alises in the WHERE clause

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.