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

>

For
select name,
priceA,
priceB
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;
suspend;
end

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com