Alpha characters in Decimal Field
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 96
- Joined: Mon May 14, 2012 1:30 pm
Alpha characters in Decimal Field
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
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
Thanks,
Ankur Saxena
Ankur Saxena
Re: Alpha characters in Decimal Field
In the Transformer you can use IsValid function to check this! You can reject if is it not a valid data.
Thanks
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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:
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.
Code: Select all
FIELD-NAME PIC S9(8)V9(2).
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
-
- Participant
- Posts: 96
- Joined: Mon May 14, 2012 1:30 pm
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.
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.
Thanks,
Ankur Saxena
Ankur Saxena
-
- Participant
- Posts: 96
- Joined: Mon May 14, 2012 1:30 pm
-
- Participant
- Posts: 42
- Joined: Tue Oct 20, 2009 8:36 am
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:
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
Last edited by DSUser2000 on Sat Jul 13, 2013 12:45 pm, edited 1 time in total.
EBCDIC Zoned DecimalDSUser2000 wrote:(no idea if that is any standard what you are describing)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
-
- Participant
- Posts: 96
- Joined: Mon May 14, 2012 1:30 pm
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.
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.
Thanks,
Ankur Saxena
Ankur Saxena
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.
FYI: there is no difference in physical storage between the two. The only difference is the valid contents. Numbers are also characters.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872