Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    Unanswered: Need a function to convert to IBM Zoned

    I need to find a function to convert decimal data to the ascii representation of IBM Zoned decimal data.

    I am not familiar with IBM Zoned.

    Any help would be greatly appreciated.

    The basic pattern is :

    Zeros filled, drop the decimal point and convert to IBM zoned decimal.

    If the number is positive then
    0 = {, 1 = A, 2 = B, 3 = C, 4 = D, 5 = E, 6 = F, 7 = G, 8 = H, 9 = I
    If the number is negative then:
    0 = }, 1 = J, 2 = K, 3 = L, 4 = M, 5 = N, 6 = O, 7 = P, 8 = Q, 9 = R

    123.45 will be converted to 00001234E for S9(07)V99 field.
    345.70 will be converted to 00003457{ for S9(07)V99 field.
    -223.47 will be converted to 00002234P for S9(07)V99 field.

    I have a function that converts zoned to decimal

    CREATE OR REPLACE FUNCTION Convert_Zoned
    (
    input IN VARCHAR2
    )
    RETURN NUMBER
    IS
    --This function will convert zoned data from a file to the
    --correct data represintation by converting the last character.
    valuec VARCHAR2(10);
    DEC VARCHAR2(1);
    SIGN VARCHAR2(1);
    valuen NUMBER(11,2);
    LOC NUMBER;
    SPEC_CASE NUMBER:=0;
    BEGIN
    IF(LENGTH(INPUT)=11) THEN
    valuec := SUBSTR(input,1,9);
    DEC := SUBSTR(input,10,1);
    SIGN := SUBSTR(input,11,1);
    IF SIGN IN ('{','A','B','C','D','E','F','G','H','I') THEN
    IF SIGN = '{'THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'0');
    ELSIF SIGN ='A' THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'1');
    ELSIF SIGN ='B' THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'2');
    ELSIF SIGN ='C' THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'3');
    ELSIF SIGN ='D' THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'4');
    ELSIF SIGN ='E' THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'5');
    ELSIF SIGN ='F' THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'6');
    ELSIF SIGN ='G' THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'7');
    ELSIF SIGN ='H' THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'8');
    ELSIF SIGN ='I' THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'9');
    END IF;
    RETURN valuen;
    ELSIF SIGN IN ('}','J','K','L','M','N','O','P','Q','R') THEN
    IF SIGN = '}'THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'0');
    ELSIF SIGN ='J' THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'1');
    ELSIF SIGN ='K' THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'2');
    ELSIF SIGN ='L' THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'3');
    ELSIF SIGN ='M' THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'4');
    ELSIF SIGN ='N' THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'5');
    ELSIF SIGN ='O' THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'6');
    ELSIF SIGN ='P' THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'7');
    ELSIF SIGN ='Q' THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'8');
    ELSIF SIGN ='R' THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'9');
    END IF;
    RETURN valuen;
    END IF;
    ELSE
    IF(INSTR(INPUT,'{')>0) THEN
    LOC := INSTR(INPUT,'{');
    ELSIF (INSTR(INPUT,'A')>0)THEN
    LOC := INSTR(INPUT,'A');
    ELSIF (INSTR(INPUT,'B')>0)THEN
    LOC := INSTR(INPUT,'B');
    ELSIF (INSTR(INPUT,'C')>0)THEN
    LOC := INSTR(INPUT,'C');
    ELSIF (INSTR(INPUT,'D')>0)THEN
    LOC := INSTR(INPUT,'D');
    ELSIF (INSTR(INPUT,'E')>0)THEN
    LOC := INSTR(INPUT,'E');
    ELSIF (INSTR(INPUT,'F')>0)THEN
    LOC := INSTR(INPUT,'F');
    ELSIF (INSTR(INPUT,'G')>0)THEN
    LOC := INSTR(INPUT,'G');
    ELSIF (INSTR(INPUT,'H')>0)THEN
    LOC := INSTR(INPUT,'H');
    ELSIF (INSTR(INPUT,'I')>0)THEN
    LOC := INSTR(INPUT,'I');
    ELSIF (INSTR(INPUT,'}')>0)THEN
    LOC := INSTR(INPUT,'}');
    ELSIF (INSTR(INPUT,'J')>0)THEN
    LOC := INSTR(INPUT,'J');
    ELSIF (INSTR(INPUT,'K')>0)THEN
    LOC := INSTR(INPUT,'K');
    ELSIF (INSTR(INPUT,'L')>0)THEN
    LOC := INSTR(INPUT,'L');
    ELSIF (INSTR(INPUT,'M')>0)THEN
    LOC := INSTR(INPUT,'M');
    ELSIF (INSTR(INPUT,'N')>0)THEN
    LOC := INSTR(INPUT,'N');
    ELSIF (INSTR(INPUT,'O')>0)THEN
    LOC := INSTR(INPUT,'O');
    ELSIF (INSTR(INPUT,'P')>0)THEN
    LOC := INSTR(INPUT,'P');
    ELSIF (INSTR(INPUT,'Q')>0)THEN
    LOC := INSTR(INPUT,'Q');
    ELSIF (INSTR(INPUT,'R')>0)THEN
    LOC := INSTR(INPUT,'R');
    ELSE
    LOC := LENGTH(INPUT);
    SPEC_CASE := 1;
    END IF;
    IF(SPEC_CASE = 1) THEN
    RETURN SUBSTR(input,1,LOC);
    ELSE
    valuec := SUBSTR(input,1,(LOC-2));
    DEC := SUBSTR(input,(LOC-1),1);
    SIGN := SUBSTR(input,LOC,1);
    IF SIGN IN ('{','A','B','C','D','E','F','G','H','I') THEN
    IF SIGN = '{'THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'0');
    ELSIF SIGN ='A' THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'1');
    ELSIF SIGN ='B' THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'2');
    ELSIF SIGN ='C' THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'3');
    ELSIF SIGN ='D' THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'4');
    ELSIF SIGN ='E' THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'5');
    ELSIF SIGN ='F' THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'6');
    ELSIF SIGN ='G' THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'7');
    ELSIF SIGN ='H' THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'8');
    ELSIF SIGN ='I' THEN
    valuen := TO_NUMBER(valuec||'.'||DEC||'9');
    END IF;
    RETURN valuen;
    ELSIF SIGN IN ('}','J','K','L','M','N','O','P','Q','R') THEN
    IF SIGN = '}'THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'0');
    ELSIF SIGN ='J' THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'1');
    ELSIF SIGN ='K' THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'2');
    ELSIF SIGN ='L' THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'3');
    ELSIF SIGN ='M' THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'4');
    ELSIF SIGN ='N' THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'5');
    ELSIF SIGN ='O' THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'6');
    ELSIF SIGN ='P' THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'7');
    ELSIF SIGN ='Q' THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'8');
    ELSIF SIGN ='R' THEN
    valuen := TO_NUMBER('-'||valuec||'.'||DEC||'9');
    END IF;
    RETURN valuen;
    END IF;
    END IF;
    END IF;
    END Convert_Zoned;
    /

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I' don't understand the reason for rolling your own when AFAIK Oracle supports Zoned-Decimal natively since V8.

    Code:
     Support of Additional Datatypes
    
    The computational usage datatype COMP-4 (COMPUTATIONAL-4) is treated as a binary datatype. The IBM-implemented computational data type, COMP-4 (also represented as COMPUTATIONAL-4, will be treated as a binary datatype.
    
    Display usage datatypes now supported are:
    
        * Over-Punch (ZONED-DECIMAL). This is the default signed numeric for the COBOL language. Digits are held in ASCII or EBCDIC format in radix 10, with one digit per byte of computer storage. The sign is held in the high order nibble of one of the bytes. It is called overpunch because the sign is "punched-over" the digit in either the first or last byte. The default sign position will be over the trailing byte. PIC S9(n)V9(m) TRAILING or PIC S9(n)V9(m) LEADING is used to specify the overpunch.
        * Display-1 Multibyte type (PIC G). This datatype is equivalent to PIC N and is used for multibyte characters.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    Reply

    My goal was to extract data from oracle tables and create a flat file containing the IBM zoned representation.

    Can COMP-4 be used in PL/SQL without PRO*COBOL ?

    If so, please offer an example.

    An external customer needs data extracted from an oracle table and converted to IBM zoned decimal format.

    I created a function to do the conversion but a previous post indicates this is an un necessary step.

    Please help.

    FYI, the function I created follows.

    FUNCTION Convert_To_Zoned
    (
    input IN NUMBER
    )
    RETURN VARCHAR2 IS
    --This function will convert decimal data from a file to the
    --correct zoned data representation by converting the last character.
    FULL_INPUT VARCHAR2(0010);
    LEADING7 VARCHAR2(0007);
    TENTH_PLACE VARCHAR2(0001) := '0';
    LAST_DIGIT VARCHAR2(0001) := '0';
    CHAR_OUT VARCHAR2(0009) := '000000000';
    BEGIN

    FULL_INPUT := LPAD(NVL(input,0),10,'0');
    LEADING7 := SUBSTR(FULL_INPUT,1,7);
    TENTH_PLACE := SUBSTR(FULL_INPUT,9,1);
    LAST_DIGIT := SUBSTR(FULL_INPUT,10,1);

    IF INPUT > 0 THEN -- first if
    IF LAST_DIGIT = '0'THEN
    LAST_DIGIT := '{';
    CHAR_OUT := LEADING7||TENTH_PLACE || LAST_DIGIT;
    ELSIF LAST_DIGIT ='1' THEN
    LAST_DIGIT := 'A';
    CHAR_OUT := LEADING7||TENTH_PLACE || LAST_DIGIT;
    ELSIF LAST_DIGIT ='2' THEN
    LAST_DIGIT := 'B';
    CHAR_OUT := LEADING7||TENTH_PLACE || LAST_DIGIT;
    ELSIF LAST_DIGIT ='3' THEN
    LAST_DIGIT := 'C';
    CHAR_OUT := LEADING7||TENTH_PLACE || LAST_DIGIT;
    ELSIF LAST_DIGIT ='4' THEN
    LAST_DIGIT := 'D';
    CHAR_OUT := LEADING7||TENTH_PLACE || LAST_DIGIT;
    ELSIF LAST_DIGIT ='5' THEN
    LAST_DIGIT := 'E';
    CHAR_OUT := LEADING7||TENTH_PLACE || LAST_DIGIT;
    ELSIF LAST_DIGIT ='6' THEN
    LAST_DIGIT := 'F';
    CHAR_OUT := LEADING7||TENTH_PLACE ||LAST_DIGIT;
    ELSIF LAST_DIGIT ='7' THEN
    LAST_DIGIT := 'G';
    CHAR_OUT := LEADING7||TENTH_PLACE || LAST_DIGIT;
    ELSIF LAST_DIGIT ='8' THEN
    LAST_DIGIT := 'H';
    CHAR_OUT := LEADING7||TENTH_PLACE ||LAST_DIGIT;
    ELSIF LAST_DIGIT ='9' THEN
    LAST_DIGIT := 'I';
    CHAR_OUT := LEADING7||TENTH_PLACE ||LAST_DIGIT;
    END IF;
    RETURN CHAR_OUT;

    END IF;
    IF INPUT < 0 THEN
    IF LAST_DIGIT = '0'THEN
    LAST_DIGIT := '}';
    CHAR_OUT := LEADING7||TENTH_PLACE ||LAST_DIGIT;
    ELSIF LAST_DIGIT ='1' THEN
    LAST_DIGIT := 'J';
    CHAR_OUT := LEADING7||TENTH_PLACE ||LAST_DIGIT;
    ELSIF LAST_DIGIT ='2' THEN
    LAST_DIGIT := 'K';
    CHAR_OUT := LEADING7||TENTH_PLACE ||LAST_DIGIT;
    ELSIF LAST_DIGIT ='3' THEN
    LAST_DIGIT := 'L';
    CHAR_OUT := LEADING7||TENTH_PLACE ||LAST_DIGIT;
    ELSIF LAST_DIGIT ='4' THEN
    LAST_DIGIT := 'M';
    CHAR_OUT := LEADING7||TENTH_PLACE ||LAST_DIGIT;
    ELSIF LAST_DIGIT ='5' THEN
    LAST_DIGIT := 'N';
    CHAR_OUT := LEADING7||TENTH_PLACE ||LAST_DIGIT;
    ELSIF LAST_DIGIT ='6' THEN
    LAST_DIGIT := 'O';
    CHAR_OUT := LEADING7||TENTH_PLACE ||LAST_DIGIT;
    ELSIF LAST_DIGIT ='7' THEN
    LAST_DIGIT := 'P';
    CHAR_OUT := LEADING7||TENTH_PLACE ||LAST_DIGIT;
    ELSIF LAST_DIGIT ='8' THEN
    LAST_DIGIT := 'Q';
    CHAR_OUT := LEADING7||TENTH_PLACE ||LAST_DIGIT;
    ELSIF LAST_DIGIT ='9' THEN
    LAST_DIGIT := 'R';
    CHAR_OUT := LEADING7||TENTH_PLACE ||LAST_DIGIT;
    END IF;
    RETURN CHAR_OUT;
    END IF;

    IF INPUT = 0 THEN
    CHAR_OUT := SUBSTR(FULL_INPUT,1,9);
    RETURN CHAR_OUT;
    END IF;
    CHAR_OUT := 'ERROR';
    RETURN CHAR_OUT;
    END Convert_To_Zoned;
    Last edited by wrwelden; 12-09-04 at 10:58.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •