Read Number that has overpunch

[mdl2]

I am writing a procedure that reads in a flat file where the numbers are defined as s9(9). Is there a format that can read an overpunch numeric.

Thanks

MDL

 

[LKBrwnDBA]

You would have to code the function yourself.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

MDL,

Oracle has no data format that recognises overpunches, but here is a function I wrote for you that should take care of business for you (provided your overpunch is in the the rightmost character).

Section 1 -- "Overpunch" function definition:

create or replace function overpunch (str_in varchar2) return number is
begin
    if nvl(length(translate(str_in,'^}JKLMNOPQR','^')),0) = length(str_in)-1 then
       return to_number(substr(str_in,1,length(str_in)-1)||
              translate(substr(str_in,-1),'}JKLMNOPQR','0123456789')) * -1;
    else
       return to_number(substr(str_in,1,length(str_in)-1)||
              translate(substr(str_in,-1),'{ABCDEFGHI','0123456789'));
    end if;
exception
    when others then
       raise_application_error(-20000,'"'||str_in||'"'||' contains either an '||
          'invalid overpunch character or some other non-numeric character.');
end;
/

Function created.
*********************************************************************************

Section 2 -- Sample overpunched data:

select * from mdl;
OVERPUNCH
---------
000000000
00000005J
00000000}
00000000{
00000005N
00000005O
00000005P
00000005Q
00000005R
          <-- Nine blank spaces
00000005A
00000005B
00000005C
00000005D
00000005E
00000005F
00000005G
00000005H
00000005I

19 rows selected.

Section 3 -- Sample invocation of OVERPUNCH function:

select overpunches,'=' "=", overpunch(overpunches) b from mdl;

            Equivalent
                Oracle
OVERPUNCH =      Value
--------- - ----------
000000000 =          0
00000005J =        -51
00000000} =          0
00000000{ =          0
00000005N =        -55
00000005O =        -56
00000005P =        -57
00000005Q =        -58
00000005R =        -59
          =
00000005A =         51
00000005B =         52
00000005C =         53
00000005D =         54
00000005E =         55
00000005F =         56
00000005G =         57
00000005H =         58
00000005I =         59

19 rows selected.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.