SCALING OF DECIMAL VALUE RESULTED IN DATA TRUNCATION

[James]

Hi,

I'm getting a "SCALING OF DECIMAL VALUE RESULTED IN DATA TRUNCATION"
error when I run a query that has an expression in it. I'm using
linked (ODBC from iSeries) tables and I think it is because the data
type is set to decimal but I can't change it because it is a linked
table. Any suggestions.

 

[SteveM]

What is your expression?

Steve

 

[James]

What is your expression?

Steve






- Show quoted text -

Very simple actually. the Sum of X / Y

 

[Jamie Collins]

Very simple actually. the Sum of X / Y

If this were Jet, X / Y would result in a value of double float (i.e.
precision of 15 significant figures) unless either X or Y (or both)
are of type DECIMAL. Also, when trying to force a value with excess
scale into a Jet column of type DECIMAL it would simply truncate the
value without error; I'm guessing your target data source instead
*does* raise an error in such circumstances.

For example, Jet DDL (ANSI-92 Query Mode):

CREATE TABLE Test (dec_col DECIMAL(6, 3) NOT NULL)
;
INSERT INTO Test (dec_col) VALUES (123.456789)
;
SELECT dec_col FROM Test
;

The insert succeeds without error and the query returns the value as
123.456 i.e. truncated.

FWIW Jet does error when the integer portion won't fit e.g.

INSERT INTO Test (dec_col) VALUES (1234);

results in an error, "The decimal field's precision is too small to
accept the numeric you attempted to add."

Jamie.

--