473,289 Members | 2,089 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

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

Jun 26 '07 #1
5 12174
sw**********@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
Jun 26 '07 #2
On Jun 26, 12:16 pm, Ed Murphy <emurph...@socal.rr.comwrote:
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.

Jun 26 '07 #3
sw**********@yahoo.com wrote:
On Jun 26, 12:16 pm, Ed Murphy <emurph...@socal.rr.comwrote:
>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
Jun 26 '07 #4
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,
<calculation1AS newcol1,
<calculation2AS newcol2
FROM Table1) AS X
INNER JOIN Table2
.....
HTH,

Plamen Ratchev
http://www.SQLStudio.com

Jun 26 '07 #5
On Tue, 26 Jun 2007 09:27:03 -0700, sw**********@yahoo.com wrote:
>On Jun 26, 12:16 pm, Ed Murphy <emurph...@socal.rr.comwrote:
>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
Jun 26 '07 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Shino | last post by:
Hi, Can anyone help with this error: "ORA-00904: invalid column name"? Thanks! SQL> create view PPFa as 2 SELECT L.UserID AS LecID, U.Name, U.Email, I.IntakeID, S.UserID AS StudID 3 FROM...
3
by: Steven de Vries | last post by:
Hi, I'am trying a very simple sql statement but it does not work. I use the SQL version 8.0 I use the "Northwind" sample database and the Table "Employees". The sql statement is: SELECT * ...
1
by: wiredog | last post by:
When I try to add the line . . . CPM * MOU AS COST, after all the CASE lines I get the response in SQL Query Analyser, Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'CPM'....
0
by: Raquel | last post by:
This simple piece of code in a try-catch block in SQLJ stored procedure is failing with CLI0611E - invalid column name: #sql sproc3_iterator = {SELECT FIRSTNME, LASTNAME FROM...
3
by: graphicsxp | last post by:
Hi, I have a SQL stored procedure which looks like that: SELECT @QuerySQL = 'SELECT as LookupField, G.GroupDesc + ' + '" : "' + ' + as DescField FROM ' + 'JOIN ItemGrouping IG on IG.CatID...
2
by: macupryk | last post by:
I build the following: USE GO CREATE PROCEDURE AddProjectQuestionContentFrequency @ProjectQuestionId int, @ProjectQuestionContent nvarchar(255), @ProjectQuestionContentFrequency...
3
by: josna | last post by:
I am trying to execute following query but i am getting a sql exception i do not understand where i went wrong. it is saying invalid column name but i have given all fields and table names...
5
by: Anne | last post by:
Hello! Here is the statement in question: --STATEMENT A SELECT * FROM dbo.myTable WHERE colX in (SELECT colX FROM dbo.sourceTable) The problem with Statement A is that 'colX' does not exist...
3
by: kayclink | last post by:
hi, i am trying to generate a report through a record in a combo box but i get this error "invalid column name "DFG55FS" Please note "DFG55FS" is a record name in my combobox. please help i am...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.