Converting Cobol Text amounts to real amounts...

  • Hi, I have a situation here that I need to find the best way to handle:

    We have some files that are extracted from Cobol (Flat Files), and loaded into SQL Server using SSIS.

    The amounts in those files are specified that way:

    So the value -> 0034255} means : -003425.5} since the } is in the negative part.

    0034255F can be translated to 003425.56 since F is the positive 6....

    Ok, I hate to say that, but that is a pain to translate to a real number, and all of that to save 1 character.. anywayz, does anyone know how to change those efficiently?

    Thanks, I've pasted the corresponding table so you can understand better.

    VALUE Positive Negative

    0 { }

    1 A J

    2 B K

    3 C L

    4 D M

    5 E N

    6 F O

    7 G P

    8 H Q

    9 I R

    Edited for readability

    Cheers,

    J-F

  • When I had to deal with a similar situation, I treated the numbers with characters as strings, and passed them through a script component that isolated the special character and used a case statement to replace it with the numeric equivalent. In my case, all of them were positive (legacy account numbers) but a similar approach might work in your situation.

  • Thanks Dave, I'll look into the script component to do a Big select case in Vb.net, that should do it! Thanks again!

    Cheers,

    J-F

  • I think I would handle this slightly differently.

    I would split the input columns so that the numeric data is in one column and the 'special' character is in another.

    I would add (in principle) three derived columns:

    1) Sign (1 for positive, -1 for negative)

    2) Value (0-9)

    3) Final Value = Sign * (orig numeric data + Value)

    To work with the special character, I would convert it to its ASCII numeric value and test whether that falls within certain ranges, rather than testing every single letter ...


Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply