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] DESCBest Regards