Path: utzoo!utgpu!watserv1!watmath!att!att!linac!pacific.mps.ohio-state.edu!zaphod.mps.ohio-state.edu!van-bc!tfic.bc.ca!clh From: clh@tfic.bc.ca (Chris Hermansen) Newsgroups: comp.databases Subject: Re: INFORMIX SQL functions Keywords: informix sql Message-ID: <1990Nov28.222220.20110@tfic.bc.ca> Date: 28 Nov 90 22:22:20 GMT References: <1990Nov20.221658.19496@informix.com> <8335@pbhyf.PacBell.COM> <1990Nov22.210621.7930@tfic.bc.ca> <19917@oolong.la.locus.com> Reply-To: clh@tacitus.UUCP (Chris Hermansen) Organization: Timberline Forest Inventory Consultants Lines: 99 In article <19917@oolong.la.locus.com> jfr@locus.com (Jon Rosen) writes: >In article <1990Nov22.210621.7930@tfic.bc.ca> clh@tacitus.UUCP (Chris Hermansen) writes: >> >>(stuff deleted) >> >>Since ISQL is apparently designed to provide a simple, quick mechanism for >>rooting around in Informix databases, why isn't there a bit more in the >>way of, oh, say transcendental functions available in ISQL? For example, >>I wouldn't mind being able to execute >> >> select a, 1.30223 * (1 - exp(-0.023 * a)) ^ 2.39045 from foo >> >>I'm aware that ESQL-C allows one to add things like this to ISQL, but this >>strikes me as a) overkill, b) an expensive way to do it, and c) a way to >>end up with a screwed-up ISQL (or at least one that Informix support might >>look upon with suspicion in the heat of a service call). >> >>I understand that Ingres provides this kind of stuff, and I really don't see >>the problem with allowing users to get at standard UN*X functions like >>exp, log, j0 (yeah, there's one I use every day), etc. >> > >Keep in mind that what you are asking for is a modification of the SELECT >statement syntax itself. It is of course easy to allow either a 3GL or >4GL to apply the appropriate functions against returned data from a SELECT >query by taking each data value and applying the formula. This could even >be done with ISQL except that ISQL is supposed to represent the exact >SQL syntax that would be used in a program. I.e., "extending" SQL with >additional functionality is not the purpose of ISQL. I guess what bugs me generally is that ISQL does have lots of extensions to ANSI SQL, otherwise (as you have noted in previous postings), it would be more or less useless. What's more, ISQL 4.xxx has several functions included already, including "DATE" functions. According to the Informix "Green Card" (boy, I sure dated myself there!), these are already extensions to ANSI Standard SQL. DATE functions, for !@#$%^ sake!!! Yessir, let's let the user convert that character string to a date. That'll make 'em really happy; talk about convenience. > >Should additional functions be included in SQL? Of COURSE!!! But the >*&@#(*($><&& people who defined SQL either knew NOTHING about the way >people actually use databases or didn't care. Scalar functions such as >Log, Exp, Abs, Cos, etc. would be relatively trivial to add to the >database engine as well as to the language. More importantly, on some >databases such as servers or database machines like Teradata, having >this functionality in the language is critical to performance. Your >example is pretty simple since the actual amount of execution time >to perform the operation is the same whether or not the data base >does the work... But, take this example: > > SELECT * FROM BLIVET > GROUP BY X > HAVING SUM(1 - EXP(-0.023 * A) > .5 (this may not make sense but > it is a good example anyway) > >poses a serious problem. Here, since the SUM of the complex expression >can't be taken by the database engine (it can't do the EXP function), >you have to retrieve all of the data and let the application do all >the work, i.e., the application becomes a data base engine itself. > >YUCCCHH!!! > >Is there hope? Maybe... Mike Stonebraker at Berkeley has proposed >object-oriented extensions to SQL (no, not making SQL a full OODMBS, >just some simple extensions) that would allow user defined data types >and user defined methods or functions on those data types. This is >sort of like the enhancement of Turbo Pascal to include objects. It >is still Pascal but it is much more powerful. Here, SQL would be >extendible to allow new functions on new datatypes (or old datatypes). >Ingres has already implemented some of these features in their new >release. Hopefully, we will see more of this. > >Jon Rosen Ahem; Turbo Pascal isn't much like Pascal in one sense; it's MISSING some crucial things from standard Pascal (like GET(), PUT(), lazy I/O). But your point is well taken. I say, put the extensions in and let the user beware. Just don't take standard things out! As a side point, a nice person from Informix dropped me a line saying that there is a pipeline that these kinds of things can get into, so LISSEN UP OUT THERE!!!! If you're an Informix user and you're cranky because you CAN convert "5/4/80" into a date but CAN'T compute LN(2.718), SAY SOMETHING!!!! Chris Hermansen Timberline Forest Inventory Consultants Voice: 1 604 733 0731 302 - 958 West 8th Avenue FAX: 1 604 733 0634 Vancouver B.C. CANADA clh@tfic.bc.ca V5Z 1E5 C'est ma facon de parler.