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