Posted by Ed Murphy on 06/26/07 17:41

sweetpotatop@yahoo.com wrote:

> On Jun 26, 12:16 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
>> sweetpota...@yahoo.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

[Back to original message]