mload rounding decimal data

[johns_maverick]

I have a decimal (17,2) field which has a default value of 999999999999999.99 (9(15).((2)). When mload'ing that data to teradata the value is being rounded to 1000000000000000.00 (1 followed by 15 0's.00) which is causing a decimal overflow error. Also the default value is being modified.
I tried the cast statement for similar data in TD and it did the same. The cast statements that I tried are as below:

select CAST
(999999999999999.99 as decimal(17,2)) as test ;
( 9(15).9(2) ) gives 1000000000000000.00 as output but,

select CAST
(99999999999999.99 as decimal(17,2)) as test;
( 9(14).9(2) ) gives 99999999999999.99 ( 9(14).9(2))

Please let me know if there is a way to have it mloaded as-is.

[dnoeth]

MLoad should easily load that decimal value without any rounding, so something different must be going wrong.

You selects are probably submitted using SQL Assistant with
Tools - Options - Answerset - Displaying Full Accuracy for BigInt and Decimal (16) and Above
unchecked.
So it's NOT rounded by Teradata, but by the grid used for SQLA.

If you check that option the decimal is returned as a string, wrong datatype, but correct value.

Are you actually shure the numeric overflow is caused that default value?
Did you check the data in the first ErrorTable?

Dieter

[johns_maverick]

Hi,
Yes I did and it is being caused by that column. I increased the size of the column from (17,2) to (18,2) and now I dont have any records going into the error table, but the data in that column is 1000000000000000.00.
I also checked to see the data in the file and the file had correct data (999999999999999.99) in it.

I checked in SQL Assistant with the option you specified checked and it worked now, but the mload still doesnt. Are there any properties that need to be set during mload to handle data like this?? Please suggest!

[rychitre]

Did you find answer? I am facing the same issue.