HELP! Formatting Decimal value to ASCII


Wed, 07/01/2009 - 18:14 — Rediranch

I need to format a decimal defined field into an ascii format, which includes being LPADded with Zeros.

Original value: 28
defined as a decimal, 15,5

The output I need for the above value:
000000002800000

In UDB you can use the command DIGITS(FIELD) where FIELD is the decimal, adn you get the above. I would prefer not to put that in my SQL Override section - can I do this formatting in Informatica Designer?

Thanks for any help!

‹ Handling COBOL files using Informatica Powercenter Memory consumption in INFA 8.6 ›
Return to forum:
actions []
Thu, 07/02/2009 - 09:46 — balakumarsakthi

HELP! Formatting Decimal value to ASCII

Hi,

In general, numerical values won't allow any formating like padding with zeros. It won't make any meaning for numerals.

If you want to formating, its better convert the numerals into string and do LPAD or RPAD.

For Example, if the original value is 28 and formated option is decimal, 15,5.

you can provide the formating condition like LPAD(to_char(Numerals * 5),15,'0').

Thanks,
bala

Thu, 07/02/2009 - 14:26 — Rediranch

Thanks. Unfortunately your

Thanks.

Unfortunately your solution loses the decimal precision, and the target system is a COBOL EBCDIC (we dont have a VSAM license, hence the ASCII). So when it reads:

000000000000028

from the LPADding, it will read it in as 0.00028

Thu, 07/02/2009 - 16:48 — Rediranch

This is what we have come up

This is what we have come up with so far for this solution. The Decimal_Field_Name is a Decimal(15,5) and the output needs to be 15 bytes with leading and trailing zeros.

IIF(IsNull(Decimal_Field_Name),'000000000000000',
lpad(substr(TO_CHAR(TO_DECIMAL(Decimal_Field_Name,5)),1,instr(TO_CHAR(TO_DECIMAL(Decimal_Field_Name,5)),'.')-1),10,'0')
||
rpad(substr(TO_CHAR(TO_DECIMAL(Decimal_Field_Name,5)),instr(TO_CHAR(TO_DECIMAL(Decimal_Field_Name,5)),'.')+1,5),5,'0'))