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

sweetpotatop (AT) yahoo (DOT) com wrote:

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

On Jun 26, 12:16 pm, Ed Murphy <emurph... (AT) socal (DOT) rr.com> wrote:
I just want to avoid the calculation again when it is already there as
my query takes a while to run already.

sweetpotatop (AT) yahoo (DOT) com wrote:

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

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

On Tue, 26 Jun 2007 09:27:03 -0700, sweetpotatop (AT) yahoo (DOT) com wrote:

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