select col1, col2, col3, ABS(somefield - :someparameter)
from sometable
order by 4

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
Sent: Thursday, June 22, 2006 10:04 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Any way to use an input parameter in an
order by clause

Hi all,

I have some records being returned that contain a timestamp field. I
would like to return the records ordered by the difference between this
field and a timestamp that I will pass in as a parameter. This will be
inside a stored procedure.

Eg

select *
from sometable
order by ABS(somefield - :someparameter)

In theory, it should return the record closest to someparameter first,
followed by the second closest etc, except I can't seem to get it to
work even with casting

The following query does indeed work as expected, it is only when I
replace the 'hard coded' timestamp with a parameter I get an issue.

select *
from sometable
order by ABS(somefield - cast('1/1/2006 9:00' as Timestamp))

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 tried explicitly casting everywhere with no luck.

Dynamic SQL Error
SQL error code = -804
Data type unknown.

FB 1.5.3 CS / WinXP.

Is there something I am missing here, or is it something that can't be
done without wrapping it in a stored procedure and manually calculating
it as an output parameter?

TIA
Adam







------------------------ Yahoo! Groups Sponsor --------------------~-->
Yahoo! Groups gets a make over. See the new email design.
http://us.click.yahoo.com/XISQkA/lOaOAA/yQLSAA/67folB/TM
--------------------------------------------------------------------~->

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links