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 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
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. 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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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 * ...
|
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'....
|
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...
|
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...
|
by: macupryk |
last post by:
I build the following:
USE
GO
CREATE PROCEDURE AddProjectQuestionContentFrequency
@ProjectQuestionId int,
@ProjectQuestionContent nvarchar(255),
@ProjectQuestionContentFrequency...
|
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...
|
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...
|
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...
|
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...
|
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"....
|
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...
|
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...
|
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 =...
|
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...
|
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...
|
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...
|
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...
| |