how to convert 99.0000 to 009900 in informatica iics

Question

In DTSDestination("Ann_Non_Comm_Prem") = FormatNbr(DTSSource("Ann_Non_Comm_Prem"),6,2) converting the input 0.0000 as 000000 and 99.0000 as 009900 what is the equivalent function to convert column value as 99.0000 and required output as 009900 in informatica.

Ex : for input 99.0000 need output as 009900

I used LPAD(COL1*10000,6,'0') but it is not working.

Please suggest a solution.

Does this answer your question? [what is the equivalent function for FormatNbr(DTSSource("Commission\_Rate\_2"), 8, 4) in informatica](https://stackoverflow.com/questions/73123643/what-is-the-equivalent-function-for-formatnbrdtssourcecommission-rate-2-8) — Luuk, Jul 29 '22 at 18:36
I gave a generic solution which can be used for any such function — Koushik Roy, Jul 30 '22 at 08:15

Koushik Roy · Accepted Answer · 2022-08-01T15:58:12.130

0

Pls use this...
(Pls note this data type would be string)

LPAD(COL1*100,6,'0')

And this is the generic solution.
Function to be replaced is FormatNbr(DTSSource("Ann_Non_Comm_Prem"),6,2). Now, look at arguments - 6 and 2. So, 6 is for 0-padding and 2 is power of 10 (10^2 or 100).

So for above example, equivalent function will be

Lpad(col1*10^2,6,'0') or lpad(col1*100,6,'0)

edited Aug 01 '22 at 15:58

answered Jul 30 '22 at 08:13

Koushik Roy

6,868
2
12
33

This is not working for me, still i am getting the output as 14600. instead of 001460 – KotaRavi Kumar Aug 01 '22 at 11:35
Because the data type isnt string its int. Pls modify the output data type to string and it will work. – Koushik Roy Aug 01 '22 at 15:35

how to convert 99.0000 to 009900 in informatica iics

1 Answers1