Alpha characters in Decimal Field

[ankursaxena.2003]

Hi Guys!!!!

I have a fixed length file and I have to extract data for AMOUNT field from fixed length field. AMOUNT is stored in flat file from 11-20 position. In target along with AMOUNT field there are many other fields also.

Sequential File -----------------> Transformer -----------------------> Target (Oracle)

I have defined Source field as VARCHAR and in target I have AMOUNT defined as DECIMAL(10,2).

Below is some of the sample data. And datastage job is converting the below sample data to 0. Do you guys have any idea?


111111111L
222222222M
333333333P

[SURA]

In the Transformer you can use IsValid function to check this! You can reject if is it not a valid data.

[ray.wurlod]

Maybe your metadata are wrong, and AMOUNT is stored only in positions 11-19. The character in position 20 means something different, and should be read as a separate field.

[FranklinE]

Your sample data looks like Cobol formatting, a "display" numeric field with the sign modifying the last byte. If your data is consistent with that pattern, your solution is to define your input column as the equivalent of the Cobol syntax:

Code: Select all

FIELD-NAME    PIC S9(8)V9(2).

If your source data is not Cobol, you should ask the developers responsible for creating it what they are doing. The "fix" is more likely with them than with you.

[ankursaxena.2003]

Thanks guys for the help.

Sura - I dont want to check if the data is valid or not as business is saying that it is valid data. They gave me a chart for conversion. For example J means that position has 1 and the number is negative. K means that it is 2 at that position and is negative and so on. Below are few examples.

12J --------> -121
19} --------> -190

Ray - Metadata is correct. AMOUNT is stored from 11-20. Business has confirmed it.

Franklin - You are correct and we got rules from business for conversion.

Guys - But, my question is that why transformer was converting the AMOUNT field to 0. Transformer should have returned an error saying that it recieved a non-numeric value in numeric field. But instead of that it converted the AMOUNT field to 0.

[chulett]

What kind of conversion are you doing in the transformer - implicit or explicit?

[ankursaxena.2003]

Chulett - It is an implicit conversion. I did not write any conversion function to convert from String To Decimal.

Source - VARCHAR
Target - Decimal

[DSUser2000]

I'm also wondering why you don't get a warning about unsuccessful conversion (I'm talking about a warning and not an explicit error; warnings don't lead to aborts if you didn't specify to "abort after 1 warning" in the job run options). What datastage version do you use?

Apart from that you will probably either have to use a complex flat file stage with appropriate format (no idea if that is any standard what you are describing) or you must operate with substrings to handle the right-most position manually in the transformer via some Convert-construction. Something like this:

Code: Select all

AsInteger((If Convert('JKLMNOPQR', '---------', column[1]) = '-' Then '-' Else '') : column[1, Len(column) - 1] : Convert('{JKLMNOPQR', '0123456789', column[1])) / 100.00

[chulett]

(no idea if that is any standard what you are describing)

EBCDIC Zoned Decimal

[FranklinE]

Craig has the right column format. The problem is that the input column is VarChar where it should be zoned decimal. You choices are either set the receiving column to VarChar and do an explicit conversion as DSUser2000 suggests, or change the input column to zoned decimal.

[ankursaxena.2003]

The source cannot be changed to Zoned Decimal as it is a fixed length file. So I have to break down the input record.

Solution:
I created a table and then I do a look up on last character. And then get the correct value.

And today I noticed that when I use StringToDecimal function then it issues warning. But, if I don't use the function then it converts to 0 without any warning. So, my concern is that DataStage should atleast give a warning or abort instead of changing to 0.

[SURA]

So, my concern is that DataStage should atleast give a warning or abort instead of changing to 0.

Why should not you raise a PMR ?

[FranklinE]

Ankur, I believe you are making an incorrect assumption. If AMOUNT always occupies positions 11-20, then you have the option to define it as zoned decimal in the sequential file stage. I would be surprised if your current column is not already set to VarChar(10), and there is no difference between that and the physical format of Decimal(10,2) with the attributes set to zoned decimal.

FYI: there is no difference in physical storage between the two. The only difference is the valid contents. Numbers are also characters.