Solved

Invalid column name....

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
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…

932 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now