Subject Value from parmeter-dependant colum in where clause
Author Tim Gahnström
I have run into a problem that I thought I had a nice solution for but my solution doesn't seem to work.

I want my select statement to return a value from a different column depending on one of the in-parameters (to a stored procedure).
If I simplify the problem I have two different price columns and which one to use is dependant on one of parameters to the SP.

I thought the select query could look like this:

select name,
case (:type = 1) then priceA
case (:type = 2) then priceB
end as Price
where Price > 10
from myTable
into name_out, price_out
do suspend;

Where PriceA and PriceB are columns in myTable

This doesn't work , I get the following error message
Column does not belong to referenced table.
Dynamic SQL Error.
SQL error code = -206.
Column unknown.

I recall that it works if I don't use Price in the where clause but that doesn't make sense, what then would be the reason for the "AS" statement?

Is there another way to return the value of a column depending on the value of an in parameter?

It is not reasonable to put the whole select in a if clause because it is rather large and I have already done that to get around a similar limitation.

Any pointes appreciated.