RE: Consistent Null Handling
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <breitliw@xxxxxxxxxxxxx>
- Date: Fri, 3 Sep 2004 15:22:49 +0200
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling
Sent: Friday, September 03, 2004 14:14
To: lex.de.haan@xxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx&subject=unsubscribe
To search the archives - //www.freelists.org/archives/oracle-l/
Other related posts:
