Jason Earl wrote:
>
> Apparently a NULL money value is not equal to zero. That is probably
> a good thing.
>
> The select you want for selecting all of the null values is:
>
> SELECT * FROM test WHERE total IS NULL;
>
> Hello,
>
> I was wondering if someone could shed some light on money fileds for me. I
> am having a problem in that a money field plus a null money field equals
> null. Try this:
>
> create table test(total money);
> insert into test values('');
> insert into test valuse(NULL);
>
> select * from test;
> total
> -----
> $0.00
>
> (2 rows)
>
> select '1'::money + total from test;
> ?column?
> --------
> $1.00
>
> (2 rows)
>
> Why is the second column blank? This is really throwing my calculations
> because there are times when $0.00 is different from no value at all.
Yes, that's the point - you can't add NULL and 0
Oracle has a NVL(FIELD,VALUE_FOR_NULL) function, that allows you to
special-case NULL-s, and I _think_ ver.6.5 will have something similar
> Also, how can I get a query of all rows where the money value is NULL?
> This fails:
>
> select * from test where total = NULL;
try SELECT * FROM TEST WHERE TOTAL IS NULL
> If I can't query for a money field with a NULL value, why can I insert one?
You can always forbid yourself installing NULLs
create table test( mymoney money not null
);
or even have a default value of 0 for the fields
create table test( mymoney money default 0
);
or both :)
create table test( mymoney money not null default 0
);
------------------
Hannu
