| Subject | Re: [firebird-support] Firebird 2.1 Dialect 3 Decimal Number Limits |
|---|---|
| Author | Helen Borrie |
| Post date | 2009-04-01T22:00:04Z |
At 07:48 AM 2/04/2009, you wrote:
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.
./heLen
>Hi ,This is stored as SMALLINT with scale of 2.
>
>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.96Sure, you increased the theoretical limit by the power of 4!
>
>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..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 KNOW ITS AN EMBARRASING QUESTION TO ASK.... please dont rub it in..
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.
./heLen