Posted by Hugo Kornelis on 06/26/07 20:29

On Tue, 26 Jun 2007 09:27:03 -0700, 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.

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

• Next in forum: sum columns into column in same table
• Prev in forum: Re: Database design question - Isolated, unrelated tables
• Thread view: Re: select alias -- invalid column name

[Reply to this message]