Solved

Invalid column name....

Posted on 2006-11-27
3
201 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

751 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