RE: Consistent Null Handling

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Fri, 3 Sep 2004 15:22:49 +0200
Message-ID: <JFEEIGBIDOCCDALDIPLNKEAOCIAA.lex.de.haan@naturaljoin.nl>

Wolfgang,
this is all about null-values *and* empty sets. see below:

SQL> create table t (c number);
Table created.

SQL> insert into t values (null);
1 row created.

SQL> commit;
Commit complete.

SQL> select sum(c), nvl(sum(c),0), sum(nvl(c,0)) from t;

  SUM(C) NVL(SUM(C),0) SUM(NVL(C,0))
-------- ------------- -------------

                     0             0

SQL> select sum(c), nvl(sum(c),0), sum(nvl(c,0)) from t   2 where 1=0;

  SUM(C) NVL(SUM(C),0) SUM(NVL(C,0))
-------- ------------- -------------

                     0

the first query might suggest that the 2nd and 3rd expression are logically equivalent, but that is rejected by the second query. and the other way around, the first query rejects the possibility that the 1st and 3rd expressions are logically equivalent.

Kind regards,
Lex.

---

visit http://www.naturaljoin.nl

---

skype me <callto://lexdehaan>

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling Sent: Friday, September 03, 2004 14:14
To: lex.de.haan_at_naturaljoin.nl
Cc: oracle-l_at_freelists.org
Subject: RE: Consistent Null Handling

I don't get that. Under what circumstances will sum(x) and sum(nvl(x,0)) give
different results? I can see it for avg, but for sum?

• Binary/unsupported file stripped by Ecartis --
• Type: text/x-vcard
• File: Lex de Haan.vcf

--

To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe To search the archives - http://www.freelists.org/archives/oracle-l/ Received on Fri Sep 03 2004 - 08:18:50 CDT