Invalid column name....

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**';
tomasdlvAsked:
Who is Participating?
 
Raynard7Connect With a Mentor Commented:
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
 
CarlWarnerCommented:
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
 
tomasdlvAuthor Commented:
the column fullname works fine. its actually the price difference that i need thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.