How To_decimal works in Informatica

0
votes

Today i came across an issue in informatica code which i have figured out the solution for it. But i don’t have a proper justification and not sure if my solution is a proper fix.

Here is the summary of my issue:

Source: Flatfile (Fixed Width)

Source Field Datatype: String 14

Target: SQL Server Table

Targer Field Datatype: Decimal (14,2)

Old Logic IIF (TO_DECIMAL(FIELDNAME),TO_DECIMAL(FIELDNAME)/100,NULL)

Bad data 00002631064132

Error Message: Transformation Evaluation Error [<> [TO_INTEGER]: integer overflow ... i:TO_INTEGER(f:TO_FLOAT(d2:TO_DECIMAL(u:'00002631064132',i:-100)),i:0)]

Updated Logic IIF(NOT ISNULL(LTRIM(RTRIM(FIELDNAME))),TO_DECIMAL(FIELDNAME)/100,NULL)

Output 26310641.32 (Loaded as Expected)

Issue Summary

We have the initial logic (Old Logic) which converts the string to decimal(14,2).

This code has been running without any issues since from a while. But today this load has failed with integer overflow (Error).

I have changed the logic as show in the picture(Updated logic) and it loaded the data successfully with expected output(Output).

My doubt is why there is to_integer, to_float showed up in the error when i’m not using anywhere in my mapping. I’m looking for proper justification for this fix.

Any thoughts?

Thanks in advance!!

1
sql-serveretlinformaticainformatica-powercenter
What is this TO_DECIMAL() function? Is this a custom function? This is tagged as sql-server, but I do not think this function is part of SQL Server. Also, ISNULL() would expect 2 arguments in SQL Server. – Matthew I
@MatthewI This is an inbuilt function in informatica. My target table is in sql server. – Meet Rohan
I would have to see the data that you are using. Can you put in a table and specify the data types of the columns? – Matthew I
It seems 2631064132 is above 2^31 and the TO_DECIMAL function doesn't handle it correctly by default. Try to enable the High Precision option in the session, it may help for this kind of values. – Mickaël Bucas
@MatthewI Hi Matthew. Thanks for your response apparently i have modified my updated logic in the iif condition to is_number function and it is also worked. – Meet Rohan

1 Answers

-1
votes

What is the data type of the variable/output port where you are logic is defined ? Also can you provide the input data that was causing this issue ?