Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: SchemaQuwery dilemma (help!)

    I want to add a number of fields together to send as a total to gateway. Unfortunately the client may leave fields blank which are entered as NULLs which can not be added!


    The Query:
    SELECT gener_amt_don + area_amt_don + region_amt_don + prog_amt_don + other_amt_don +disast_amt_don AS total, id_don, user_don, repeat_don, gener_don, gener_amt_don, area_don, area_amt_don, region_don, region_amt_don, prog_don, prog_amt_don, repeat_don
    FROM donate

    I need to be adding numbers for "total" to have a value.
    The form which inserts has fields,(amounts (amt_x)), which are not populated. I would think that the script below would enter the default 0.00. But it is entering NULL which you can not be add to total.

    The table:

    CREATE TABLE `donate` (
    `id_don` int(100) unsigned NOT NULL auto_increment,
    `user_don` int(6) default NULL,
    `repeat_don` int(50) default NULL,
    `comm_don` varchar(250) default NULL,
    `com_add_don` varchar(50) default NULL,
    `com_city_don` varchar(50) default NULL,
    `com_st_don` varchar(50) default NULL,
    `com_zip_don` varchar(25) default NULL,
    `com_desc_don` varchar(250) default NULL,
    `com_notice_don` int(11) default NULL,
    `gener_don` int(6) unsigned default NULL,
    `gener_amt_don` decimal(8,2) default '0.00',
    `area_don` int(6) default NULL,
    `area_amt_don` decimal(8,2) default '0.00',
    `region_don` int(6) default NULL,
    `region_amt_don` decimal(8,2) default '0.00',
    `prog_don` int(6) default NULL,
    `prog_amt_don` decimal(8,2) default '0.00',
    `disast_amt_don` decimal(8,2) default '0.00',
    `other_don` varchar(256) default NULL,
    `other_amt_don` decimal(8,2) default '0.00',
    `total_don` int(6) default NULL,
    `cdate_don` date default NULL,
    `edate_don` date default NULL,
    `time_don` timestamp NOT NULL default CURRENT_TIMESTAMP,
    PRIMARY KEY (`id_don`)
    ) ENGINE=MyISAM AUTO_INCREMENT=294 DEFAULT CHARSET=latin1;

    A typical result is :290 304 1 NULL NULL NULL NULL NULL NULL NULL 1 10.00 NULL NULL 0 NULL 211 NULL NULL NULL NULL NULL NULL 2009-06-24 2009-06-24 18:36:33

    Nick

  2. #2
    Join Date
    Jun 2009
    Posts
    6
    Try adding 'not null' to those fields, since the calculation requires a value.

    _________________________________
    Embedded database systems | raima.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •