How to convert the signed decimal

[dvpawankumar]

Anyone from this help me with below two issues.

1. I have the signed decimal columns.

Eg: 00000000456}
00000000000}

At the record having special characters. How to convert this into the number.

2. I have the records like this

Eg: 4,256.56
-1,435.89

How to remove those commas .

Please help me on this.

[bcarlson]

What is the input datatype? Is it EBCDIC from the mainframe?

For #1, you have data that looks like 000000456}. This looks suspiciously like data that was incorrectly converted from EBCDIC to ASCII. If the input is PIC 9(9) you can translate that either into character or numeric with the standard EBCDIC to ASCII text conversion.

On the other hand, if the input is signed, PIC S9(9), then the last byte isn't a normal EBCDIC number character. The sign is going to SHARE the byte with the number.

For the sake of example, let's use a 4 digit number, PIC S9(4). The value is 1234+.

Code: Select all

EBCDIC value if PIC 9(4)
========================
FFFF
1234

EBCDIC value if PIC S9(4)
=========================
FFFD
1234

The EBCDIC number characters are F0-F9 and directly correspond to 0-9. In ASCII, these are 48-57. In our unsigned example above, the last byte is F4. However, when the number is signed, the last byte is represented with C0-C9 for negative numbers and D0-D9 for positive numbers.

Your example was '00000000456}' I am guessing that if you looked at the mainframe/EBCDIC hex value representation you would see this:

Code: Select all

FFFFFFFFFFFD
000000004560

If you convert this to their exact ASCII values, you would get '00000000456}' because D0 in EBCDIC maps to '}'

In DataStage, specify this field as either as an integer or a decimal. For an integer, make the data format 'text' and make sure the field is signed (singed is the default - you have to explicitly indicate that the field is extended or unsigned).

For a decimal, again, make the data format 'text'. Then, in the packed option, set it to " packed = no (zoned)" and set the sub-option 'Signed' to trailing or leading, depending on where the sign is on your input column.

Hopefully I have not misdiagnosed your issue and gone off on an unneeded tangent. The symptom just looked very familiar with the '}' in your numeric data.

Brad.