Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: ( 3000 - 3300 ) = large numbers

    hi,
    can anyone please tell me what can i do with this problem.
    i have table in my localhost
    and a very simple query
    Code:
    SELECT
    datesold
    , ( auctionprice - principal ) as 'Net'
    , auctionprice
    , principal
    FROM sold_tbl where datesold = '2009-01-31'
    it results just fine and what i really need.
    it also output the negative number when smaller substracts to a larger number.
    it is just in my localhost
    but when i try this query in my webhost's phpmyadmin
    it gives me wrong result when smaller substracts to a larger number.
    it gives me very large number and not negative number.
    e.g.
    3000-3300 = -300 ( in my localhost )
    3000-3300 = 18446744073709551316 ( in my webhost )

    they all have the same table structure.
    what seems to be the problem here?

    pls, thanks in advance
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please do a SHOW CREATE TABLE for each of your tables

    i'll bet that you have declared one of the columns as UNSIGNED
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    Code:
    CREATE TABLE IF NOT EXISTS `sold_tbl` (
      `idsold_tbl` int(10) unsigned NOT NULL auto_increment,
      `idpawnshop_tbl` varchar(45) collate latin1_general_ci default NULL,
      `DateLoan` datetime default NULL,
      `LastName` varchar(45) collate latin1_general_ci default NULL,
      `FirstName` varchar(45) collate latin1_general_ci default NULL,
      `Address` text collate latin1_general_ci,
      `Principal` int(10) unsigned default NULL,
      `AmountWords` text collate latin1_general_ci,
      `ItemType` varchar(45) collate latin1_general_ci default NULL,
      `NetProceeds` int(10) unsigned default NULL,
      `Interest` float default NULL,
      `ServiceCharge` float default NULL,
      `DST` int(10) unsigned default NULL,
      `Description` text collate latin1_general_ci,
      `Note` text collate latin1_general_ci,
      `AuthoRep` varchar(45) collate latin1_general_ci default NULL,
      `MaturityDate` date default NULL,
      `ExpiryDate` date default NULL,
      `ServiceType` varchar(45) collate latin1_general_ci default NULL,
      `Branch` varchar(45) collate latin1_general_ci default NULL,
      `Grams` float unsigned default NULL,
      `Karats` varchar(45) collate latin1_general_ci default NULL,
      `Quantity` int(10) unsigned default NULL,
      `AuctionPrice` int(10) unsigned default NULL,
      `DateSold` date default NULL,
      `SoldTo` varchar(45) collate latin1_general_ci default NULL,
      PRIMARY KEY  (`idsold_tbl`),
      KEY `Index_2` (`DateLoan`,`LastName`,`ItemType`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=269 ;
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  4. #4
    Join Date
    Mar 2004
    Posts
    480
    two things

    1) you don't have auction price or principal in this table

    2) your problem stems from your use of FLOAT as your column type in the fields you have here and likely in the other table (if there is one).

    FLOAT is used for very large or very small numbers and should not be used as you have when you should most likely be using DECIMAL. DECIMAL is not subject to the same rounding errors.

  5. #5
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    thanks for the help.
    i made a View Table
    thats why the fields that i present here is not available on the table
    well i remove the unsigned in the Principal field
    and also in AuctionPrice field
    and it works fine now.

    thanks
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

Posting Permissions

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