On 9 Jun 2004 at 3:43, dleec45 wrote:

>How do I write a query which will return the closest value in the
>where statement. If I have a bunch of numbers in a table that has
>values like 100, 200, 300, 400 and my where clause looks for 249 or
>250, how do I return 200 or 200 and 300, whatever the case may be?

IMHO, the simplest way - stored procedure. Something like this:

CREATE PROCEDURE (A_VALUE INTEGER)
RETURNING (whatever_you_want) AS
DECLARE VARIABLE CLOSEST_FROM_BOTTOM INTEGER;
DECLARE VARIABLE CLOSEST_FROM_TOP INTEGER;
BEGIN
SELECT MAX(THE_FIELD) WHERE THE_FIELD<=:A_VALUE INTO
:CLOSEST_FROM_BOTTOM;
SELECT MIN(THE_FIELD) WHERE THE_FIELD>:A_VALUE INTO
:CLOSEST_FROM_TOP;

/* Here you do whatever you wand and select whatever you want */

END^
SY, Dimitry Sibiryakov.