colincoleman2002 wrote:
I thought I new this but im am stummped, I have a Domain that we have defined as Decimal(6,2) We have launched our project and started importing data into the fields all goes well until we find that our imported figure of 30,000,000.00 is converted into -12,949,672.96.
So I thought id change the Domain to say DECIMAL 18,2 ...Wey Hey it now lets me store 30,000,000.00 correctly.
In Helens Book/Bible on Page 134 DECIMAL data type, it says that DECIMAL(4,1) WILL STORE ±214,748,364.7 , AS I WAS USING 6,2 WHERE DID I GO WRONG?
Ann W. Harridon answers:
The 6 in decimal(6,2) is the precision. It includes the decimal digits. So the legal range is not -999,999.99 to +999,999.99 but -999,999 to +999,999 and the scale factor is added later by multiplying by 10 to the scale factor minus one.
Firebird stores fixed precision numbers as binary integers of various sizes - 2 byte, 4 byte, and 8 byte. Firebird enforces the size limit of the integer type, not the decimal limit. A two byte integer can store values from -32768 to +32767 so numbers with a precision of 4 or less are stored in two byte integers.
Numbers with a precision between 5 and 9 are stored in four byte integers, which is what you got when you asked for decimal (6,2). That has a range from -2,147,483,648 to +2,147,483,647. The value 30,000,000.00 is stored as 3,000,000,000, so it overflows the type and rolls over into a negative number.
Numbers up to 18 digits are stored in 8 byte integers, and they get pretty big. Back when InterBase started, it would hold the gross national debt (of the US) expressed in milli-Lira. Since then the national debt has grown and Italy has switched to the euro.
Helen Borrie answers:
Meaning DECIMAL(6,2) could store <= 21,474,836.47, of course....so 30,000,000.00 is well out of range in this case.
I won't "rub it in" - but the purpose of that comment was to WARN you that the implementation of DECIMAL is somewhat anomalous by SQL rules. AFAIK, it hasn't changed since; but implementations are subject to correction from version to version. Just because it's possible now to stuff a DECIMAL type past its theoretical overflow limit is never a reason to define fields with the "certain knowledge" that you can depend on such anomalies forever. Always define fields according to SQL rules, not according to known anomalies; otherwise you can pretty much guarantee that it will bite you some day!
Actually, these days there's no good reason to define numeric or decimal columns as pinchy little numbers like (6,2) even if, down the track, you never try to overstuff them. Who really cares nowadays whether a column is 4 bytes or 8 bytes? OTOH, if you really do want to save those 4 bytes, and you firmly believe that it's never going to overflow, then use 9 as your precision. That wouldn't have saved you from your current mistake, though. Spend the extra 4 bytes on life insurance: for a particular domain you can use a CHECK constraint to impose maximum and minimum limits if you want to.
colincoleman2002 wrote:
I now see where I went wrong, What is the difference between DECIMAL and NUMERIC ....?
Ann W. Harrison answers:
In Firebird, there is not difference. The standard says that a numeric field is restricted to the declared precision, but that a decimal field may accept larger values up to some implementor defined limit. So in Firebird, both decimal and numeric values behave like decimal values.