select alias -- invalid column name

[sweetpotatop@yahoo.com]

Hi,

I got 'Invalid Column Name NewCol1' when I query the following:

Select col1, col2, (some calculation from the fields) as NewCol1,
(some calculation from the fields) as NewCol2,
NewCol1 = NewCol2 from
Table1 inner join Table2 inner join Table3....
Where
.....

Basically, I want to find out if NewCol1 = NewCol2 after the
calculation

Any advice?

Thanks in advance. Your help would be greatly appreciated.
Wanda

[Ed Murphy]

Re: select alias -- invalid column name

sweetpotatop@ya hoo.com wrote:

I got 'Invalid Column Name NewCol1' when I query the following:
>
Select col1, col2, (some calculation from the fields) as NewCol1,
(some calculation from the fields) as NewCol2,
NewCol1 = NewCol2 from
Table1 inner join Table2 inner join Table3....
Where
.....
>
Basically, I want to find out if NewCol1 = NewCol2 after the
calculation

You can try this:

select col1, col2, (...) as NewCol1, (...) as NewCol2,
case when NewCol1 = NewCol2 then 'equal' else 'not equal' end

but I don't think that works. This should definitely work:

select col1, col2, (...) as NewCol1, (...) as NewCol2,
case when (...) = (...) then 'equal' else 'not equal' end

[sweetpotatop@yahoo.com]

Re: select alias -- invalid column name

On Jun 26, 12:16 pm, Ed Murphy <emurph...@soca l.rr.comwrote:

sweetpota...@ya hoo.com wrote:

I got 'Invalid Column Name NewCol1' when I query the following:

>

Select col1, col2, (some calculation from the fields) as NewCol1,
(some calculation from the fields) as NewCol2,
NewCol1 = NewCol2 from
Table1 inner join Table2 inner join Table3....
Where
.....

>

Basically, I want to find out if NewCol1 = NewCol2 after the
calculation

>
You can try this:
>
select col1, col2, (...) as NewCol1, (...) as NewCol2,
case when NewCol1 = NewCol2 then 'equal' else 'not equal' end
>
but I don't think that works. This should definitely work:
>
select col1, col2, (...) as NewCol1, (...) as NewCol2,
case when (...) = (...) then 'equal' else 'not equal' end

I just want to avoid the calculation again when it is already there as
my query takes a while to run already.

[Ed Murphy]

Re: select alias -- invalid column name

sweetpotatop@ya hoo.com wrote:

On Jun 26, 12:16 pm, Ed Murphy <emurph...@soca l.rr.comwrote:

>sweetpota...@y ahoo.com wrote:

>>I got 'Invalid Column Name NewCol1' when I query the following:
>>Select col1, col2, (some calculation from the fields) as NewCol1,
>>(some calculation from the fields) as NewCol2,
>>NewCol1 = NewCol2 from
>>Table1 inner join Table2 inner join Table3....
>>Where
>>.....
>>Basically, I want to find out if NewCol1 = NewCol2 after the
>>calculation

>You can try this:
>>
> select col1, col2, (...) as NewCol1, (...) as NewCol2,
> case when NewCol1 = NewCol2 then 'equal' else 'not equal' end
>>
>but I don't think that works. This should definitely work:
>>
> select col1, col2, (...) as NewCol1, (...) as NewCol2,
> case when (...) = (...) then 'equal' else 'not equal' end

>
I just want to avoid the calculation again when it is already there as
my query takes a while to run already.

I think the server will recognize and optimize the duplication. If
you're concerned it won't, though, then you could use a temp table:

create table #foo (
col1 type, col2 type, NewCol1 type, NewCol2 type, match int
)

insert into #foo (col1, col2, NewCol1, NewCol2, match)
select col1, col2, (...), (...), 0

update #foo set match = 1 where NewCol1 = NewCol2

[Plamen Ratchev]

Re: select alias -- invalid column name

You can use a derived table, like this:

SELECT X.col1,
X.col2,
X.newcol1,
X.newcol2,
CASE WHEN X.newcol1 = X.newcol2
THEN 'Equal'
ELSE 'Not equal'
END AS compare
FROM (
SELECT col1,
col2,
<calculation1 AS newcol1,
<calculation2 AS newcol2
FROM Table1) AS X
INNER JOIN Table2
.....


HTH,

Plamen Ratchev

http://www.SQLStudio.com

[Hugo Kornelis]

Re: select alias -- invalid column name

On Tue, 26 Jun 2007 09:27:03 -0700, sweetpotatop@ya hoo.com wrote:

>On Jun 26, 12:16 pm, Ed Murphy <emurph...@soca l.rr.comwrote:

>sweetpota...@y ahoo.com wrote:

I got 'Invalid Column Name NewCol1' when I query the following:

>>

Select col1, col2, (some calculation from the fields) as NewCol1,
(some calculation from the fields) as NewCol2,
NewCol1 = NewCol2 from
Table1 inner join Table2 inner join Table3....
Where
.....

>>

Basically, I want to find out if NewCol1 = NewCol2 after the
calculation

>>
>You can try this:
>>
> select col1, col2, (...) as NewCol1, (...) as NewCol2,
> case when NewCol1 = NewCol2 then 'equal' else 'not equal' end
>>
>but I don't think that works. This should definitely work:
>>
> select col1, col2, (...) as NewCol1, (...) as NewCol2,
> case when (...) = (...) then 'equal' else 'not equal' end

>
>I just want to avoid the calculation again when it is already there as
>my query takes a while to run already.

Hi sweetpotatop,

Instead of using a temp table as Ed suggests, you can better use a
derived table:

SELECT col1, col2, NewCol1, NewCol2,
CASE WHEN NewCol1 = NewCol2 THEN 'equal' ELSE 'not equal' END
FROM (SELECT col1, col2, (...) AS NewCol1, (...) AS NewCol2
FROM YourTable
WHERE Something = SomethingElse) AS D;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis