Обсуждение: Problems with money fields.

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.
Also, how can I get a query of all rows where the money value is NULL?
This fails:

select * from test where total = NULL;
ERROR:  parser: parse error at or near "null"

and this gives me the rows with $0.00:

select * from test where total = '';  
total
-----
$0.00
(1 row)

If I can't query for a money field with a NULL value, why can I insert one?

Thank you,
Matthew Hagerty

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
fieldplus 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
differentfrom no value at all.  Also, how can I get a query of all rows where the money value is NULL?  This fails:
 
  select * from test where total = NULL;  ERROR:  parser: parse error at or near "null"
  and this gives me the rows with $0.00:
  select * from test where total = '';    total  -----  $0.00  (1 row)
  If I can't query for a money field with a NULL value, why can I insert one?
  Thank you,  Matthew Hagerty

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