Expression to replicate Aggregator

Question

I need to replicate below piece of sql expression in informatica where COLUMN1 is Decimal(25,6) :

replace(round(coalesce(trim(L '0' FROM COLUMN1),0),0),'.000000','')

I tried using the below in my aggregator expression :

IIF(ISNULL(COLUMN1),0,(ROUND(LTRIM(COLUMN1,0),0)))

encountered the below error when validated the same:

[ROUND]: operand cannot be converted to a number

Please help me fix this issue

Answer 1

It's failing coz you are applying a string function LTRIM to COLUMN1 implicitly converting it to a string and then you are applying ROUND to a string.

A way to solve this would be to just use ROUND. Since COLUMN1 is a decimal, you don't need to strip the left zeroes as they won't be there.

Answer 2

Please use this functions it will work.

1. v_port-- IIF(ISNULL(COLUMN1),'0',LTRIM(COLUMN1,0))
2. v_port1(datatype(decimal)--- TO_DECIMAL(v_port)
3. o_port ROUND(v_port1,2)--(how many character decimal like 1 or 2 just i mentation 2)

it will work.