Re: Calculating square root in sql statement ????

>From: weller@zorro.cecer.army.mil (Bonnie Weller) >Subject: Calculating square root in sql statement ???? >Date: 21 Jul 1994 17:10:59 GMT >X-Informix-List-Id: <news.7722> > >Hello > >I have SE 5.01 ISQL 4.11. I do NOT have 4GL (which I understand can >do square roots.) > >I need to compute the following into an sql statement. > > _____________________ >\\ | 2 2 > \\| (x1-x2) + (y1-y2) < r1+r2 > >I am not sure how to do the square root part of the equation as isql does >not have this capability. > >So far I have >SELECT * from file1 >WHERE >((x1-field1)(x1-field1) + (y1-field2)(y1-field2)) < r1+field3> >(where x1,y1, and r1 are some given value) > >There is a squared root option in the UNIX utility. > >sqrt (expression) > >Is it possible that within isql, control can be passed to a simple shell >script, whose output is a varaible (with a value, which is a square root) >Can this variable (value) be used by isql? If so, how do I go about it ? This is a prime case for a little bit of rethinking about the problem statement. I'd recommend reading both of Jon Bentley's books 'Programming Pearls' and 'More Programming Pearls'. One of them has a discussion of a closely related problem; I can't remember which, and my copies are out on loan to someone... You can rewrite the expression as: SELECT * FROM File1 WHERE ((x1-x2)*(x1-x2) + (y1-y2)*(y1-y2)) < (r1+field3)*(r1+field3); and this will return the same set of data. Alternatively, you can use: SELECT * FROM File1 WHERE sqrt((x1-x2)*(x1-x2) + (y1-y2)*(y1-y2)) < r1 + field3; This works in 5.0x and later engines. You also have the various trigonometric and algebraic functions -- COS, SIN, TAN, ATAN, ATAN2, EXP, POW, LOGN, LOG10, ABS, MOD, ROOT. These are documented in the Version 6.00 Informix Guide to SQL: Syntax book. I don't think they were documented previously. The only unusual function in the collection is ROOT; if takes 2 arguments, the value for which you want the Nth root, and the value N (which can, in fact, be omitted and defaults to 2, giving you the square root). ROOT(X, N) is equivalent to POW(X, 1/N), in other words. Note that both ROOT and POW work with fractional exponents. I believe the implementation is by direct calls to the corresponding C library functions, using doubles as arguments. Thus, treat the values returned for 32-digit DECIMALs with a touch of caution; the answer is probably only (!) good for 14 digits or so, which is generally adequate. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>