473,326 Members | 2,076 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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)
Jun 25 '09 #1
4 2717
ck9663
2,878 Expert 2GB
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
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
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
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

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

Similar topics

5
by: malcolm | last post by:
Example, suppose you have these 2 tables (NOTE: My example is totally different, but I'm simply trying to setup the a simpler version, so excuse the bad design; not the point here) CarsSold {...
2
by: aj70000 | last post by:
This is my query select ano,max(date),a_subject from MY_TAB where table_name='xyz' and ano=877 group by a_subject,ano order by a_subject ANO max(Date) A_Subject 877 2005-01-20...
11
by: randi_clausen | last post by:
Using SQL against a DB2 table the 'with' key word is used to dynamically create a temporary table with an SQL statement that is retained for the duration of that SQL statement. What is the...
2
by: William Roberts | last post by:
I have created a simple select query (bound to a form in Access 2K) that calls my own public function and returns a result to an existing field in a table. I use the AS clause to reference the...
3
by: Razzie | last post by:
Hi, I know that as a general rule, whenever your class contains members that implicitly or explicitly implement IDisposable, your class should too. However, does it count when my class uses a...
1
by: Anonieko | last post by:
Understanding and Using Exceptions (this is a really long post...only read it if you (a) don't know what try/catch is OR (b) actually write catch(Exception ex) or catch{ }) The first thing I...
5
by: pwiegers | last post by:
Hi, I'm trying to use the result of a conditional statement in a where clause, but i'm getting 1)nowhere 2) desperate :-) The query is simple: -------- SELECT idUser,...
5
by: Dave | last post by:
I need to filter an Access 2000 result set in ASP 30 using the ADO recordset.filter. I build the filter in pieces. The first clause of the filter is this... WHERE word LIKE 'S%' ... to...
0
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...
10
by: Chris H | last post by:
Greetings, I'm trying to update an address field with "standard" abbreviations so that I can do a comparison of various accounts to one another on the address. I can update a set of records for...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.