select alias -- invalid column name

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

Re: select alias -- invalid column name

sweetpotatop@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

Re: select alias -- invalid column name

On Jun 26, 12:16 pm, Ed Murphy 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.

Re: select alias -- invalid column name

sweetpotatop@yahoo.com wrote:

> On Jun 26, 12:16 pm, Ed Murphy 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

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,
AS newcol1,
AS newcol2
FROM Table1) AS X
INNER JOIN Table2
.....


HTH,

Plamen Ratchev
http://www.SQLStudio.com

Re: select alias -- invalid column name

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

>On Jun 26, 12:16 pm, Ed Murphy 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