problem applying a WHERE condition on an aliased response

Question

This has been driving me nuts. I hope it's not been asked before but I could not find the answer so I will try to explain the situation as best as I can.

my objective is to get data of products and their colour versions based upon a set of criteria like brand, category, price and so forth.

the basic query works fine in that it returns all the data as needed.

as a functional requirement, the client can filter the results based upon the price of the products.

a product's base price can be overridden by a colour price instead. so for example, the product costs 69.99 but the Teal colour costs 39.99

the product table has a field pPrice that contains the price - the override comes as an if statement:

if (pc.iColourPrice != 0, pc.iColourPrice, p.pPrice) as pPrice

however, if the client applies a price filter, say 50 to 70, it appends at the end of the query:

and pPrice between 50 and 70

unfortunately, this looks to the products.pPrice and ignores the override from the prodContainer table through iColourPrice. the data set ends up having products cheaper than the requested filter.

the question is, how do I apply a 'where' filter that will take the adjusted pPrice into account?

Answer 1

you could write the filter like this:

and if(pc.iColourPrice != 0, pc.iColourPrice, p.pPrice) between 50 and 70

(aliases can not be used in a WHERE clause: Problems with Column Aliases)

Answer 2

Rename pPrice from the if and in the query write the new name.

Answer 3

Use HAVING instead of WHERE

SELECT if (pc.iColourPrice != 0, pc.iColourPrice, p.pPrice) as pPrice
FROM pc [+ JOINS]
WHERE [ + conditions]
HAVING pPrice BETWEEN 50 AND 70

Having happens in a sense after the select, and after any GROUP BY clauses, so can use the aliases.