[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

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**';
0
tomasdlv
Asked:
tomasdlv
1 Solution
 
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
 
Raynard7Commented:
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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now