column alias in where clause

Hi all,
I have the following query that uses a subquery for one of the columns.
However if I try to use this subquery column in the where clause with the
alias I've given it, it tells me it is an invalid column. Here's the query:
select a.*,(select companyID from contacts where contactID = a.contactID) as
compID
from appointment a
where compID in(select companyID from tsClientUsers where UserID=5) or
compID is null
It keeps giving me the error that compID is an invalid column name. why? And
how can I get around this? I cannot do an inner join because not all
appointments have a related contact.
Any help would be much appreciated!
--
SaraSara, try this:
select * from
(
select a.*,(select top 1 /*just in case when subquery returns more than one
value*/companyID from contacts where contactID = a.contactID) as
compID
from appointment a )p
where compID in(select companyID from tsClientUsers where UserID=5) or
compID is null
Regards,
Tomislav Kralj
"Sara" <s@.mail.com> wrote in message
news:E5241E9D-47B0-40F5-AD0D-CA8B06347755@.microsoft.com...
> Hi all,
> I have the following query that uses a subquery for one of the columns.
> However if I try to use this subquery column in the where clause with the
> alias I've given it, it tells me it is an invalid column. Here's the
> query:
> select a.*,(select companyID from contacts where contactID = a.contactID)
> as
> compID
> from appointment a
> where compID in(select companyID from tsClientUsers where UserID=5) or
> compID is null
> It keeps giving me the error that compID is an invalid column name. why?
> And
> how can I get around this? I cannot do an inner join because not all
> appointments have a related contact.
> Any help would be much appreciated!
> --
> Sara|||The WHERE clause has no idea about the alias, because it is processed long
before the expressions in your SELECT list. So, in most cases, you will
either have to repeat the expression in the WHERE clause, or use a subquery
like Tomislav suggests.
However in this case I believe it is possible to avoid all those subqueries
and use a LEFT OUTER JOIN:
DECLARE @.userID INT
SET @.userID = 5
SELECT a.*, c.companyID
FROM appointment a
LEFT OUTER JOIN Contacts c
ON a.contactID = c.contactID
LEFT OUTER JOIN tsClientUsers t
ON a.compID = t.compID
WHERE t.userID = @.userID
-- you may also try AND instead of WHERE here
If you provide DDL, sample data and desired results (see
http://www.aspfaq.com/5006), I might even be able to verify that my above
solution works in your case.
On 3/15/05 3:15 AM, in article
E5241E9D-47B0-40F5-AD0D-CA8B06347755@.microsoft.com, "Sara" <s@.mail.com>
wrote:

> Hi all,
> I have the following query that uses a subquery for one of the columns.
> However if I try to use this subquery column in the where clause with the
> alias I've given it, it tells me it is an invalid column. Here's the query
:
> select a.*,(select companyID from contacts where contactID = a.contactID)
as
> compID
> from appointment a
> where compID in(select companyID from tsClientUsers where UserID=5) or
> compID is null
> It keeps giving me the error that compID is an invalid column name. why? A
nd
> how can I get around this? I cannot do an inner join because not all
> appointments have a related contact.
> Any help would be much appreciated!|||On Tue, 15 Mar 2005 00:15:03 -0800, Sara wrote:
(snip)
>It keeps giving me the error that compID is an invalid column name. why? And[/color
]
Hi Sara,
That's a result of the defined order of evaluation for SELECT
statements. In theory, the order is:
1. Build temp result set from FROM clause (including all joined tables).
2. Remove rows not satisfying the WHERE clause
3. Create groups, according to GROUP BY clause
4. Remove all rows from groups not satisfing the HAVING clause
5. Build final result set, as specified in SELECT clause, based on data
in temp result set.
(6. Reorder the result set, based on ORDER BY - technically, this is a
cursor operation and not part of the SELECT statement).
(In practice, most products will change the order of evaluation for
optimization, as long as the results don't change)
As you can see, the building of the SELECT clause is the final step;
that's the reason that aliasses can't be used in the rest of the query
(though they can be used in the ORDER BY).
>how can I get around this? I cannot do an inner join because not all
>appointments have a related contact.
You could use a derived table (as suggested by Tomislav), but in this
case I believe there's a better solution:
SELECT a.Col1, a.Col2, ..., a.ColN, -- Never use SELECT * !!
c.CompanyID AS compID
FROM Appointments AS a
LEFT OUTER JOIN Contacts AS c
ON c.ContactID = a.ContactID
WHERE c.CompanyID IN (SELECT CompanyID
FROM tsClientUsers
WHERE UserID = 5)
OR c.CompanyID IS NULL
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)