On Sun, Jul 13, 2003 at 11:14:15PM -0700, Vincent Hikida wrote:
> Oops forgot to cc the list.
> > I just happened to notice another difference recently between Oracle and
> > Postgresql for the clause
> >
> > WHERE 1 IN (1,2,NULL)
> >
> > In Oracle, this clause is false because 1 compared to a NULL
This is a great technique. It is especially useful in finance for
compounded interest for problems like the following
total return = ((1+janReturn)(1+febReturn)(1+marReturn))-1
I first learned it from an MBA in finance when I was looking over a
spreadsheet that she wrote.
Vincent Hikida,
Member
Jean-Christian Imbeault wrote:
> Why is it that "select null + 1" gives null but "select sum(a) from
> table" where there are null entries returns an integer?
>
> Shouldn't the sum() and "+" operators behave the same?
---
SQL92 (6.5 ):
1) Case:
a) If COUNT(*) is specified, then the result is
Jean-Christian Imbeault wrote:
Shouldn't the sum() and "+" operators behave the same?
No, see SQL99, Section 6.16, General Rules 1.b:
"Otherwise, let TX be the single-column table that is the result of
applying the to each row of T and eliminating null
values. If one or more null values are eli
Oops forgot to cc the list.
> Unfortunately, intra-row functions using nulls return nulls. Inter-row
> functions "usually" ignore the nulls. I think there may be a few
exceptions.
> Though there is a relational theory which has is rigorously consistent,
> nulls are not part of the theory. Nulls ar
The
select null + 0
is not the same as the
select sum(a) from a
statement.
Something equivalent would be
select sum(a) where a in (select null as a union select 1 as a)
In other words: As far as I understand it, sum() sums up all non null
values. In statement you have only one value, which h
On Mon, Jul 14, 2003 at 12:54:55AM -0500, Ron Johnson wrote:
> SQL is only one possible relational query language. It didn't
> become de facto standard until the mid- to late-80s.
>
> It is an outgrowth of SEQEL (Structured English QuEry Language),
> which was IBM's 1st try at a descriptive q
On Sun, 2003-07-13 at 10:17, [EMAIL PROTECTED] wrote:
> > I understand that SQL is the interface between apps and RDBMS's because
> > of history, not because it is correct design. Could you point me to a
> > link or book or paper that deals with this paradox? Thanks!
>
> I'm not sure what you mea
Why is it that "select null + 1" gives null but "select sum(a) from
table" where there are null entries returns an integer?
Shouldn't the sum() and "+" operators behave the same?
TAL=# select null + 0;
?column?
--
(1 row)
TAL=# select * from a;
a
---
1
(3 rows)
TAL=# select sum(a) fr
