Re: Is this stuff really SQL 92 compliant

From: Michael Haldey <michaelh_at_genasys.com.au>
Date: Fri, 12 Aug 1994 03:48:38 GMT
Message-ID: <CuEL92.E23_at_genasys.com.au>


In article <327vr4$dgu_at_pong.lasc.lockheed.com> z50816_at_mip.lasc.lockheed.com (Terry Greenlaw) writes: <...>
>
>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.

I think that's correct. If you are comparing with NULL, the result is NULL, not TRUE or FALSE. This is a sort of 3 values logic, not 2 values.

>
>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?
>

No. Because you can not compare with NULL. If you need to get the value, exclude rows that contain NULL.

>------------------------------------------------------------------------
>
>Problem 2)
>
>select * from T where c1 between 0 and 1 returns the first row of table T
>above.
>
>select * from T where c1 between 1 and 0 returns no rows.

According to the Oracle SQL manual,

        between x and y

means

        x <= value <= y

so your second sentence is wrong.

>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

Michael

-- 
Michael Haldey  |  Genasys II Pty Ltd, Sydney, Australia
                |  Internet: michaelh_at_genasys.com.au
		|
		|  #include  <std_disclaimer.h>
Received on Fri Aug 12 1994 - 05:48:38 CEST

Original text of this message