From oracle-l-bounce@freelists.org Fri Sep 3 10:56:18 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i83FuGU24019 for ; Fri, 3 Sep 2004 10:56:16 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i83FuGI24014 for ; Fri, 3 Sep 2004 10:56:16 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1FEBB72C3EF; Fri, 3 Sep 2004 10:58:49 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 05381-86; Fri, 3 Sep 2004 10:58:49 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8900172C314; Fri, 3 Sep 2004 10:58:48 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Date: Fri, 3 Sep 2004 08:59:29 -0700 (PDT) From: Steve Rospo X-X-Sender: srospo@sapphire.wa.watchmark.com To: oracle-l@freelists.org Subject: RE: Consistent Null Handling In-Reply-To: <1094217237.41386e15234bd@webmail.hosting.telus.net> Message-ID: MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 9109 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: srospo@watchmark.com Precedence: normal Reply-To: srospo@watchmark.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Take a table with all nulls in x but at least one row: sum(x) => null sum(nvl(x,0)) => 0 nvl(sum(x),0) => 0 Take a table with no rows: sum(x) => null sum(nvl(x,0)) => null nvl(sum(x),0) => 0 S- On Fri, 3 Sep 2004, Wolfgang Breitling wrote: > 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? > > Quoting Lex de Haan : > > > Note however that the following three Oracle expressions are (and always > > have been) *different* > > that is, they give *different* results under certain conditions: > > > > - sum(x) > > - sum(nvl(x,0)) > > -- To unsubscribe - mailto:oracle-l-request@freelists.org&subject=unsubscribe To search the archives - http://www.freelists.org/archives/oracle-l/