fractional truncation issue

rzandieh · Post by **rzandieh** » Thu Oct 05, 2006 10:26 am

have two fields A and B both are decimal 7,2
in datastage I am adding the two fields into field C and it is also a decimal 7,2 through transformer stage A+B --> C
None of the original values from A and B are even close to being 7,2
but I am getting warning message fractional truncation on them.
the underlying database is sql server

Thanks in advance

ArndW · Post by **ArndW** » Thu Oct 05, 2006 10:37 am

What is your exact error message and in which stage is this happening?

rzandieh · Post by **rzandieh** » Thu Oct 05, 2006 12:19 pm

ArndW wrote:What is your exact error message and in which stage is this happening? ...

The error message we get is :

SAddData..Transformer_20.DSLink19: DSD.BCIPut call to SQLExecute returned informational message.
SQLSTATE=01004, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver]Fractional

in the transformer we only add A+B from input link to populate the field C

Thanks

kris007 · Post by **kris007** » Thu Oct 05, 2006 12:29 pm

rzandieh wrote: SAddData..Transformer_20.DSLink19: DSD.BCIPut call to SQLExecute returned informational message.
SQLSTATE=01004, DBMS.CODE=0

This is an informational message.

rzandieh wrote: [DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver]Fractional

It looks incomplete to me. Can you post the complete error?

thumsup9 · Post by **thumsup9** » Thu Oct 05, 2006 12:31 pm

Are you trying to insert Decimal value into an Integer, it looks an issue with datatype.

ArndW · Post by **ArndW** » Thu Oct 05, 2006 12:40 pm

It looks like we are one step closer. The error is that the values that DataStage are sending to the database don't match that DDL's format. You stated that in DataStage you have a numeric with 7,2 defined; but what is the actual database definition for that column?

rzandieh · Post by **rzandieh** » Thu Oct 05, 2006 1:27 pm

I looked at the underlying data

col A+B ---> C

col A is 7,2 in datastage and in actual database field definition
col B is 7,2 in datastage and in actual database field definition
col C was actually 9,2 and not 7,2 I changed the datastage to match
the actual definition for col C. so now both datastage and actual
database field definition are 9,2 .
I still get

SQLSTATE=01004, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver]Fractional truncation

Thanks

kris007 · Post by **kris007** » Thu Oct 05, 2006 2:02 pm

Did you also update the display settings in the Columns tab?

ray.wurlod · Post by **ray.wurlod** » Thu Oct 05, 2006 2:56 pm

The "fractional truncation" message is generated by SQL Server (you can discern this from the square bracketed information in the message). It may be that SQL Server has some data with more than two decimal places and warns about the truncation when delivering a DECIMAL(x,2). It may also be that there's a bug in SQL Server decimal arithmetic that generates a spurious warning. Search the SQL Server forums around the place.

rzandieh · Post by **rzandieh** » Thu Oct 05, 2006 3:18 pm

I checked the display and it was correct.
I do believe I have resolved the issue
I had to use combination iconv and oconv ,it is working,
it seems that in sql server when it is
adding multiple fields that are decimals (in this case 7,2)
even though they are defined as 7,2 when adding it expands
beyond scale of 2.
by using iconv and oconv with MD2 it works now.