Re: Is this stuff really SQL 92 compliant

In article <nvl.jthill_at_us.oracle.com>, jthill_at_upsizeme.us.oracle.com. (Jim Hill - MVS SQL*Net) writes:
> TOG wrote:
> >table T contains:
 

> > C1 C2
> > -- --
> > 1 2
> > 2
 

> >"select max(c1) from t" returns 1.
> >"select max(c2) from t" returns 2.
> >"select greatest(c1,c2) from t"
> > returns 2 for the first record
> > but it returns NULL for the second.
 

> >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?
>
> But that's not what you're asking for. You're asking for the greatest
> value, and the value of C1 is unknown. 'NULL' should imvho have been
> 'unknown' If you want to treat 'unknown' as 'irrelevant', use NVL to
> lowball it, e.g.
> select greatest(nvl(c1,-1e100),nvl(c2,-1e100)).
>
> Jim

My claim is that min() and least() should treat nulls in the same fashion.

min() returns the lowest NON-NULL value. least() should do the same.

Either least() should ignore nulls (my opinion), or min(c1) should return null if any row has a null value for c1 (non-compliant SQL). Following your suggestion, we would have to use max(nvl(c1,-1e100)) also in case any of the rows had nulls.

P.S. The reason this all came up is that Forms 4.0.12.4 core dumps if you have a statement of the form a := least(:f1,:f2,:f3), and one of the fields is null.

Terry Greenlaw on site at |---------Word association question #14---------- z50816_at_mip.lasc.lockheed.com |Compuserve is to the Internet like __ is to Unix

Encompass Technologies       |  a) Buffalo     c) A $200.00 copy of DOS
greenlaw_at_crl.com             |  b) DOS         d) A $200.00/month copy of DOS