Re: [INTERFACES] Problems with money fields.

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