Oracle vs. Informix: richer SQL command set ???

> Subject: Re: Does Informix Provide Poor Product support ??? > Date: Wed, 12 Oct 1994 17:41:51 GMT > Reply-To: gburnore@netcom.com (Gary L. Burnore) > Organization: the home office in Souix City, IA > > Ade Barkah (mbarkah@slate.mines.colorado.edu) wrote: > : David Harold Lipschitz (David@lipdav.demon.co.uk) wrote: > : ... > : : And although Oracle is more expensive and more difficult to support > : : and potentially slower than Informix, it has a much richer SQL command > : : set. I can write single SQL statements in Oracle that require > : : hundreds of lines of I-4GL code. ... > > : Just curious... how many Informix SQL lines would it take to > : do the same job ? > > : -Ade Barkah > > AN even more interesting statement: Give one example of a single sql > statenemt in oracle that would require hundreds of lines of I-4gl code. > (Since 'hundreds' means > 100 I assume you're exagurating to make your > point, so how about the longest one you can think of.) I'd really like > to see this because I might consider changing to oracle if this is > _really_ the case. > -- > gburnore@garys.arasmith.com How about: UPDATE some_table SET state = 'too many' WHERE row_ident IN ( SELECT row_ident FROM some_table GROUP BY row_ident HAVING count(*) > some_limit ) Informix will not let you update a table that is used in a subquery. Oracle says "no problem". Usually a subquery can be restated as just a condition on the table to be updated, but I am not sure how to do that with the SQL statement above. David's "hundreds of lines" may be hyperbole, but to do the above in Informix would require at least a temporary table and two to three times the number of tokens. I count words / tokens rather than lines, since tokens vary less than lines in different coding styles. As you see, I write LOTS of rather short lines. The formatting abilities of Oracle's SQL*Plus are *wonderful*. Just two examples: 1) Oracle allows you to sort on columns not in the select list, (actually, this is in the underlying SQL language, not in SQL*Plus) and 2) Oracle's "break on" syntax allows suppression of duplicates and line skipping, so you can produce the following kind of report very easily: state city ----------- -------------- Colorado Denver Boulder Pueblo California San Jose Menlo Park Michigan Lansing On the other hand, I have heard that SQL*Report is truly brain dead, tho' I have not used it. So in comparing more-or-less corresponding products: Oracle relation Informix ------ -------- -------- SQL language > SQL language SQL*Plus >> ISQL / DBaccess (language features) SQL*Plus < ISQL / DBaccess (editing features, environment) SQL*Report << ACE / 4GL reports It's the same old thing: Each product does some things better, some things worse than the competition. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\