Re: Simple select using calculated column

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 03/03/04 

Date: Wed, 3 Mar 2004 18:49:16 +0100

You cannot refer to a column alias in the query (the one exception is in
ORDER BY).

So either use the same function call in the WHERE clause, or use a derived
table:

SELECT newcol
FROM
(
select dbo.function(column) as newcol
from tablename
) as der
WHERE newcol = 0 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Earl Teigrob" <earlt777@hotmail.com> wrote in message
news:OEoRDYUAEHA.3308@TK2MSFTNGP10.phx.gbl...
> I have a query like
>
> select dbo.function(column) as newcol
> from tablename
> where newcol=0
>
> everything works fine without the where clause
> but when I add it, I get an error that newcol is an invalid column name
>
> I am sure that I have done this before (quite a while ago) but can not
> remember the syntax
>
> Thanks for the help
>
> Earl
>
>

Relevant Pages

  • Re: Alter table and update with ADO
    ... > alter table ... > add NEWCOL integer; ... >The query fails with 'Field newcol not found'. ...
    (borland.public.delphi.database.ado)
  • Alter table and update with ADO
    ... I am using an ADO SQLQuery to check to see if structure updates are needed ... AND Name = 'NEWCOL' ... The query fails with 'Field newcol not found'. ...
    (borland.public.delphi.database.ado)
  • Simple select using calculated column
    ... I have a query like ... from tablename ... everything works fine without the where clause ... I get an error that newcol is an invalid column name ...
    (microsoft.public.sqlserver.programming)