Subject Re: [firebird-support] Value from parmeter-dependant colum in where clause
Author Martijn Tonies
> 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:
> For
> 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

select name,
end as Price
where Price > 10
from myTable
into name_out, :v_PriceA, :v_PriceB
do begin
if (type = 1)
then price_out = v_PriceA;
else price_out = v_PriceB;

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Upscene Productions