problem applying a WHERE condition on an aliased response

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?

From stackoverflow

• 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)

  Dimitar Christoff : this works although it has made a bit of a mess of my left side filtering of results - i think i can work around it though. thanks.

  Dimitar Christoff : so the answer is, you can't use aliased / assumed / assigned values in where statements, you need to eval everything again?

  najmeddine : yes, see the link I added in my answer.

  True Soft : make a view with the column pPrice evaluated with the if, and then select from that view with 'WHERE pPrice BETWEEN 50 AND 70'

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

  Dimitar Christoff : by doing so, say renamed to newprice, eg if (pc.iColourPrice != 0, pc.iColourPrice, p.pPrice) as newprice, i get an error: Unknown column 'newprice' in 'where clause'

  Majkel : To use it like that you would have to: `select * from ( [your query with newprice alias] ) where newprice between 50 and 70`

  Dimitar Christoff : hrm although i can see how that might work, i have a completely different problem which is this: i need to create a set of filters as 'slap-on' sqls that are global for the sitenav and are then used in the sidemenus as well. taking this route would really mean a major rewrite ...

• 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.