Subject Re: Any way to use an input parameter in an order by clause
Author Adam
--- In, "Rick Debay" <rdebay@...> wrote:
> select col1, col2, col3, ABS(somefield - :someparameter)
> from sometable
> order by 4

Hi Rick,

Already tried that, it does not work ;)

Hence I wrote:

> I have also tried moving this logic into the select with the intention
> of using the order by [fieldnumber] syntax, but it has the same problem.

I have got around the problem in a way I am not totally happy with,
but is acceptable. Due to the way it works, it is nearly impossible
for the procedure to return more than 5 records, and it is unlikely to
return more than one or two.

I have changed it to a for select loop, which calculates the
difference inside. I then have a stored procedure that selects first 1
from the stored procedure ordered by the difference.

I do not like it because Firebird must entirely build the results from
the first procedure before return.