SQL:  Invalid column name
Application Type
Traditional Web

Hi,

I want to create the following SQL query, with an extra calculated attribute. But I can't get it to work or find a solution.

SELECT {PricesStoreA}.[ItemNo], {PricesStoreB}.[nr], {PricesStoreA}.[PriceA],  {PricesStoreB}.[PriceB],  ({PriceStoreA}.[PriceA] - {PriceStoreB}.[PriceB]) AS pd
FROM {MasterData}  
    Inner JOIN {PricesStoreA} ON {PricesStoreA}.[ItemNo] = {MasterData}.[No_]
    Inner JOIN {PricesStoreB} ON {MasterData}.[Nr] = convert(varchar(11), {PricesStoreB}.[nr])
WHERE pd != 0
ORDER BY {PricesStoreA}.[ItemNo] DESC

This is my structure:



Without the "AS pd" and "WHERE pd !=0" it works just fine, but when i want it to filter to show only the records with a difference, it gives me an invalid column name error. 

I know I could recalculate the difference after WHERE, and that works, but I want to know what i'm doing wrong.

Hi Donny,


The column that you want to filter is not accessible on "where clause" because this is calculated on select (occurs after where's run).

Change "pd != 0" to "({PriceStoreA}.[PriceA] - {PriceStoreB}.[PriceB]) != 0". Or you can do the same query in a sub-query without where the clause and the main query do the filter something like this:


SELECT  T.[ItemNo], T.[nr], T.[PriceA],  T.[PriceB], T.pd

FROM (SELECT {PricesStoreA}.[ItemNo], {PricesStoreB}.[nr], {PricesStoreA}.[PriceA],  {PricesStoreB}.[PriceB],  ({PriceStoreA}.[PriceA] - {PriceStoreB}.[PriceB]) AS pd

FROM {MasterData}  

    Inner JOIN {PricesStoreA} ON {PricesStoreA}.[ItemNo] = {MasterData}.[No_]

    Inner JOIN {PricesStoreB} ON {MasterData}.[Nr] = convert(varchar(11), {PricesStoreB}.[nr])) T

WHERE T.pd != 0

ORDER BY T.[ItemNo] DESC

Best Regards

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.