At 5:09 AM -0500 7/21/99, Para-dox wrote:
>Import file qtest.txt contains:
>
>133575,70625,26400.00,"O"
>123456,70626,76400.12,"A"
>
>
>table setup is:
>CREATE TABLE QTEST (
>INVNUM INT,
>DATE INT,
>AMOUNT DECIMAL(7,2),
>TYPE CHAR(10));
>
>import command:
>
>LOAD DATA LOCAL INFILE 'qtest.txt' INTO TABLE QTEST FIELDS TERMINATED BY ','
>OPTIONALLY ENCLOSED BY '"';
>
>Results:
>+--------+-------+---------+------+
>| INVNUM | DATE | AMOUNT | TYPE |
>+--------+-------+---------+------+
>| 133575 | 70625 | 9999.99 | O |
>| 123456 | 70626 | 9999.99 | A |
>+--------+-------+---------+------+
>
>Why hath the amount forsaken me?
Your type is DECIMAL(7,2), and the range that can be represented with
that type is -999.99 (7 characters) to 9999.99 (7 characters). When
MySQL encounters an out-of-range value, it clips it to the appropriate
endpoint of the range and stores the result. That's what you're seeing.
Tables created in MySQL 3.23 and up will see a range of -99999.99 to
999999.99 with DECIMAL(7,2).
--
Paul DuBois, paul@stripped
