Topic: Re: How can you use a column created in Select stament in wheres clause

Reply | New Topic | This is SPAM | This is Offensive

Submitted: 6/10/2008 4:23:19 PM

By: Aaron Bertrand [SQL Server MVP]

Since the select list is parsed almost last, you cannot use aliases you
create there in other parts of the query (the exception is ORDER BY).

You either need to repeat the expression in the where clause, or use a
subquery or CTE.

SELECT col1, col2, total = col1 + col2
FROM dbo.table1
WHERE col1 + col2 > 0;

SELECT col1, col2, total
FROM (SELECT col1, col2, total = col1 + col2
FROM dbo.table1) x
WHERE total > 0;

WITH x(col1, col2, total) AS
(
SELECT col1, col2, col1 + col2
FROM dbo.table1
)
SELECT col1, col2, total
FROM x
WHERE total > 0;

"TheVillageCodingIdiot" <whosyodaddy1019@hotmail.com> wrote in message
news:ee7dc120-c620-478e-8e08-de5a8910ba37@26g2000hsk.googlegroups.com...
> example
>
> Select col1, col2, (col1+col2) as total
> from table1
> where total > 0
>
> How do you can you use the Total column in your wheres clause. When I
> do something like the statment above i get a error that Total is a
> invalid column.

Replies below ↓

Replies

Reply | New Topic | This is SPAM | This is Offensive

Submitted: 6/10/2008 4:23:19 PM

By: Aaron Bertrand [SQL Server MVP]

Re: How can you use a column created in Select stament in wheres clause

Reply | New Topic | This is SPAM | This is Offensive

Submitted: 6/10/2008 4:43:23 PM

By: Tom Cooper

Re: How can you use a column created in Select stament in wheres clause

The processing of the where clause is done before the select, so when you do
the where total > 0 total doesn't exist yet. To avoid this either don't use
total in the where clause, eg,

Select col1, col2, (col1+col2) as total
from table1
where col1+col2 > 0

or use a derived table

Select col1, col2, total
From (Select col1, col2, (col1+col2) as total
from table1) As x
where total > 0

Tom

"TheVillageCodingIdiot" <whosyodaddy1019@hotmail.com> wrote in message
news:ee7dc120-c620-478e-8e08-de5a8910ba37@26g2000hsk.googlegroups.com...
> example
>
> Select col1, col2, (col1+col2) as total
> from table1
> where total > 0
>
> How do you can you use the Total column in your wheres clause. When I
> do something like the statment above i get a error that Total is a
> invalid column.

Reply | New Topic | This is SPAM | This is Offensive

Submitted: 6/10/2008 4:43:23 PM

By: Tom Cooper

Re: How can you use a column created in Select stament in wheres clause

Reply | New Topic | This is SPAM | This is Offensive

Submitted: 6/10/2008 4:51:55 PM

By: TheVillageCodingIdiot

How can you use a column created in Select stament in wheres clause

example

Select col1, col2, (col1+col2) as total
from table1
where total > 0

How do you can you use the Total column in your wheres clause. When I
do something like the statment above i get a error that Total is a
invalid column.

Page 1 of 2 Pages