Decimal problem

Ultramundane · Post by **Ultramundane** » Thu Mar 23, 2006 10:15 am

Using ODBC Enterprise Stage if I use the metadata that was imported, Ascential corrupts my decimal values that do not have any precision. That is, I have decimal(18, 0) columns and Ascential corrupts the values and sets them to 2147483647 or -2147483648. If I change the datatype in Ascential to Decimal(20, 2) same issue. However, if I use user defined SQL and do a convert to DECIMAL(20,2) in the SQL then it works. Columns that have a precision and scale greater than 0 in the database (18, 1), (20, 2), etc... seem to work fine. It does not work when the scale = 0.

What is wrong?

ray.wurlod · Post by **ray.wurlod** » Thu Mar 23, 2006 6:49 pm

Is Scale 0 or is Scale empty? (If the latter there's a problem with the import process - try importing Orchestrate schema definition using dbutil to access the table.)

The two values you've cited are, of course, the minimum and maximum possible int32 values; DataStage treats Decimal without a scale (that is, with scale empty) as int32. Try putting an explicit 0 into the Scale column and let us know whether that helps.

Ultramundane · Post by **Ultramundane** » Thu Mar 23, 2006 8:44 pm

Scale is empty. I have tried to specify a scale, but Ascential blanks out a 0. I tried to import and get same thing. I have placed a call with Ascential and they thought it was pretty serious. They give me a P1 case.

Thanks for your help,
Ryan

Ultramundane · Post by **Ultramundane** » Fri Mar 24, 2006 1:54 pm

Just got patched that fixed. Ecase 91460.

Tx.

Ultramundane · Post by **Ultramundane** » Wed Apr 12, 2006 11:28 am

Ultramundane wrote:Just got patched that fixed. Ecase 91460.

Tx.

The patch did not fix the issue. It caused another bug where decimals would get truncated both before and after the decimal point. Example,
123456.9999 would get loaded as 123450.9900

Ascential released another patch.
Ecase 91572.

This caused another problem with extreme rounding. This time with what appears to be an int64 instead of int32.

Ascential is working on yet another Ecase to fix.
Ecase 92288.

I think it is crazy that I need 3 different Ecases to fix one reported issue because Ascential cannot test properly.

ray.wurlod · Post by **ray.wurlod** » Wed Apr 12, 2006 4:29 pm

Perhaps you could raise another request with IBM (it's not Ascential any longer) to test their patches?

Ultramundane · Post by **Ultramundane** » Thu May 25, 2006 1:59 pm

Got patch for Ecase 92288. I still get warnings about implicit datatype conversions from decimal to ints that Ascential may not be able to make properly when storing decimal(x,0) as decimal(x,0), where x is natural number less than 39.

I also wanted to test more on the decimal and ODBC. Found that a decimal(38, 38 ) does not work either. Ascential is trying to convert an incoming decimal(38, 38 ), defined as decimal(38, 38 ), to a decimal(35, 38 ) and then to store that as a decimal(38, 38 ).