Arithmetic Overflow prevention

Question

Arithmetic Overflow prevention

Express12

SSCoach

Points: 18514
More actions
August 2, 2006 at 8:50 am

#169219

Looking for a SQL feature to enhance a failing INSERT.   My process is attempting to INSERT a column value into TAB_A but is failing on an Arithmetic Overflow.
Source value rendered: -102613.5000
Target column definition: DECIMAL (9,4)
Can you recommend a SQL feature that will somehow round, truncate, cast, convert, etc.. the source data value to a DECIMAL (9,4) attribute -- allowing it to be inserted?
SPEC's:
SOURCE: INSERT INTO TAB_A (COL_1) SELECT COL_ZZZ FROM TAB_B WHERE Account_ID = '1234'
        (fyi, COL_ZZZ value: -102613.5000    COL_1 is defined as DEC (9,4)

TARGET: TAB_A COL_1 [decimal](9, 4) NOT NULL
INSERT failing w/ Arithmetic Overflow trying to insert value: -102613.5000 into DEC (9,4) column

BT

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Bhushan Default port Points: 1427 More actions · Answer 1

the decimal value contains 10 digits (including precision & scale) where as data type is defined as decimal(9,4). Change the data type from decimal(9,4) to decimal(10,4)

Express12 SSCoach Points: 18514 More actions · Answer 2

No can do. The obvious enhancement would be to alter the column to DECIMAL (10,4) or greater. BUT this has ripple effects downstream to additional receiving columns also defined as DEC (9,4)...

I'm looking for a SQL feature to trim the incoming value to DEC (9,4) Any idea's?

BT

Express12 SSCoach Points: 18514 More actions · Answer 3

I think I'll try this: SELECT CAST(-102613.5000 AS DECIMAL(9,3))

(substituting my column name in place of the numeric value listed above AND adding CASE statements to interrogate the actual value > 99999.9999)

BT

Sergiy SSC Guru Points: 110208 More actions · Answer 4

What do you mean "trim".

Which way you want to trim value -102613.5000 to DEC(9.4) ?

_____________
Code for TallyGenerator

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 5

INSERT INTO MyTable (My9_4_Num)

SELECT My10_4_Num

FROM MyOtherTable

WHERE My10_4_Num < 99999.9999

Accomplishes your goal of "Arithmetic Overflow prevention".

I know of no way to shove a decimal(10,4) number into a decimal(9,4) column without altering the number by shifting the decimal one digit to the left.

INSERT INTO MyTable (My9_4_Num)

SELECT CASE WHEN My10_4_Num > 99999.9999

THEN CAST(My10_4_Num*.1 AS decimal(9,4))

ELSE My10_4_Num END

FROM MyOtherTable

Andy

Sergiy SSC Guru Points: 110208 More actions · Answer 6

So, you gonna transform -102613.5000 into -10261.3500

Are you sure these values are equal?

_____________
Code for TallyGenerator

Express12 SSCoach Points: 18514 More actions · Answer 7

Points well taken. There's absolutely no way to fit a DEC (10,4) into a DEC (9,4) without distorting the value. What I wound up doing was implementing a predicate to ONLY perform the INSERT IF the range of data values was between -99999.9999 and +99999.9999

The fallout here is that data in a range outside of these values will be bypassed (and our business has accepted this for now)

thx for your feedback.

BT