Solved

Invalid column name....

Posted on 2006-11-27
3
205 Views
Last Modified: 2008-03-06
I have this query which returns the difference in price of securities. i need to filter out the results of price_difference by say between '.01' and '-.01' but everytime i do this i get 'Invalid column name'


SELECT t1.secsym, t1.name, t1.price AS [Axys Price], t2.price AS [Moxy Price], t1.price - t2.price AS Price_Difference
FROM axyshld AS t1 INNER JOIN moxypos AS t2 ON t1.Secsym = t2.Secsym
UNION
SELECT  t1.Secsym, t1.Name, t1.price AS [Axys Price], 0 AS [Moxy Price], t1.price AS Price_Difference
FROM axyshld AS t1 LEFT JOIN moxypos AS t2 ON t1.Secsym = t2.Secsym
WHERE ((t2.secsym IS NULL) and (t2.secsym not like 'ca**'))
UNION SELECT t2.Secsym, t2.fullname, 0 AS [Axys Price], t2.price AS  [Moxy Price], -t2.price AS Price_Difference
FROM moxypos AS t2 LEFT JOIN axyshld AS t1 ON t1.Secsym = t2.Secsym
WHERE t1.secsym IS NULL and t1.secsym not like 'ca**';
0
Comment
Question by:tomasdlv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 33

Expert Comment

by:CarlWarner
ID: 18021462
You are doing a UNION with a column named "name" and trying to make it work with a column named "fullname".  If you change it to "t2.fiullname AS name", it should work.
0
 

Author Comment

by:tomasdlv
ID: 18021522
the column fullname works fine. its actually the price difference that i need thanks
0
 
LVL 35

Accepted Solution

by:
Raynard7 earned 125 total points
ID: 18022924
The problem is that your where statement is only going to operate on the last select statement, t1 is not included in this statement (basically everything between unions is a separate statement)

so you can either make all that you have above a sub query, ie select * from (unionQueryGoesHere) q1 where q1..... = .... or you can move your where conditions from the bottom to the actual location where you require them.
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question