Re: Is this stuff really SQL 92 compliant

In article <Cun5Cz.LnF_at_gremlin.nrtc.northrop.com>, msallwas_at_world.nad.northrop.com (Michael Sallwasser) writes:
> In article Terry Greenlaw replies:
> >In article Jim Hill writes:
> >>In article Terry Greenlaw originally wrote:
> >>
> >>>Shouldn't least and greatest have the same functionality on columns that
> >>>min and max do on rows in that you're asking for the greatest or least
> >>>KNOWN value?
>

[lots of good stuff deleted for sake of psuedo-brevity]

> As someone else pointed out, this is a question that people have been
> struggling with for awhile. It really is a matter of opinion, IMHO. Oracle
> SQL does appear to me to be consistent: all group functions (except
> count(*)) ignore null values -and- all other functions (except NVL) return
> a null value when operating on any null value. The two exceptions, though
> inconsistencies, are so for good reason.
>
> But then again, maybe I too easily become an apologist for the status quo. :-)
> --
> ============================================================================
> Michael Sallwasser | Down one path is utter dispair and hopelessness. Down
> Northrop Grumman | the other is total destruction. Let us choose wisely.

The other exceptions to functions ignoring nulls are concat(), replace(), decode(), and dump(). Things should really heat up as soon as Oracle treats nulls and empty strings as different values, which means that all code will have to be rewritten that uses concat() or || to join strings. Every place that you do an a||b where a and/or b could be null will have to be replaced with nvl(a,'')||nvl(b,'') or the expression will cast to null. I can hardly wait ;-}

The ignoring of nulls in rowwise group operations makes perfect sense to me. However, columnar group operations (least(), greatest()) need the same functionality to eliminate tons of exception code.

The reason all this came about is that we are working on a general purpose query builder and display tool for our end-users. When working on the code to deal with row to column transformations, we started getting core dumps in Forms 4.0. We traced them down to a bug which chokes on nulls in least() and greatest() calls. We have no way of knowing which function the user will pick, so low- (or high-) balling becomes a real headache. To make it worse, support for user-defined functions in Oracle 7.1 will have to be added. At least then we'll be able to add our own least_known() and greatest_known() calls.

Terry Greenlaw (on-site) z50816_at_mip.lasc.lockheed.com Encompass Technologies greenlaw_at_crl.com

----------------------- Oracle error of the week ------------------------
FRM-47111: Cannot copy value to item <name>

    Cause: You tried to use the copy built-in on an image item.    Action: Find some other way to copy or reproduce the data. Received on Wed Aug 17 1994 - 19:03:07 CEST