Re: Please help with SQL
1. SELECT *
FROM tab1 A
WHERE koef = (SELECT koef FROM tab1 B GROUP BY koef HAVING MIN(ABS(keyval-koef)));
I've not tested this for syntax or semantics, but the gist of it is about
right. It looks for the koef which has the lowest difference from the target
value (I called it keyval). There is probably a better way of handling this,
but I haven't the time to spend working it out. The MIN(ABS(keyval-koef)) part
is critical!
2. Yes, it's called ABS(), if you are using a recent enough version
like 6.00 and above, I think. Try it in 5.0x but don't be surprised if it isn't there.
Note that you can write a stored procedure in place of the ABS() function if it
isn't built-in. It won't be as fast, but it will work:
CREATE PROCEDURE ABS(n DECIMAL) RETURNING DECIMAL; IF n < 0 THEN LET n = -n; END IF;
RETURN n;
END PROCEDURE;
-- syntax unchecked.
3. Using ESQL/C, you control the precision. Using ISQL or DB-Access, you have
almost no control over it. There is a environment variable DBFLTMASK which may
work for you, but there again may not. Set it to the number of decimal places
you want shown.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
>From: "Vitaly E. Kursitis" <vital@tiny.rtc.neva.ru>
>Date: Thu, 21 Nov 1996 17:01:05 +0300
>X-Informix-List-Id: <news.30729>
>
>Hi all.
>I'm new in informix and I've got some silly questions that I can't
>seem to find the answers to.
>
>I'm working on Informix SE v5.01.
>
>1. There is a table "tab1" that has astrucure like this:
>
>! koef ! value1 ! value2 !
>! float ! float ! float !
>
>Using ESQL/c I calculated koef and I need to find the nearest
>koef in this table in order to get the corresponding value1 and
>value2 from there. How can I do it using SQL?
>
>2. Is there a system function like abs() that works with float
>numbers?
>
>3. How can I set the precision for output values? (value1 and value2
>fields stores numbers with 3 floating point (1.234) but after
>"select * from tab1" I got 1.23 only).
>
>Thanks in advance.
>Vitaly