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

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

    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;

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Check again the requirements with your customer because COMP-4 is not the same as ZONED DECIMAL
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    Pardon me. A previous post pointed me in that direction. I need to convert my data to the IBM representation of signed numeric data in text format. See the function I included in my original post.

    I was told that Oracle built in features could accomplish the conversion I desire.

    Thanks,

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    I believe there is no Oracle built-in function to do this type of conversion, but you are wellcome to try this more generic function:
    Code:
    Function Convert_To_Zoned
    (
     Input 		In Number       -- Number To Convert
    ,Maxlen   	In Number := 10 -- Output Length
    ,Decimals 	In Number := 0  -- Decimals (Scale)
    )
    Return Varchar2 Is
    -- This function will convert decimal data to the
    -- correct zoned data representation
    Input_Nbr	Number; 
    Full_Input	Varchar2(30); 
    Last_Digit	Char(1) := '0';
    Is_Minus  	Number(2):=0;
    Char_Out 	Varchar2(30);
    Begin 
      Input_Nbr	:= Nvl(Input,0)*(10**Decimals);
      Is_Minus	:= Sign(Input_Nbr);
      Input_Nbr	:= Abs(Input_Nbr);
      Full_Input	:= To_Char(Input_Nbr,'FM'||Lpad('0',Maxlen,'0'));
      Last_Digit 	:= Substr(Full_Input,-1);
      Char_Out 	:= Substr(Full_Input,1,Maxlen-1);
      If Is_Minus = -1 Then
        Char_Out := Char_Out||Translate(Last_Digit,'0123456789', '}JKLMNOPQR');
      Else
        Char_Out := Char_Out||Translate(Last_Digit,'0123456789', '{ABCDEFGHI');
      End If;
      Return Char_Out;
    End Convert_To_Zoned;
    /

    Last edited by LKBrwn_DBA; 12-09-04 at 16:02.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    Thank you. Your assistance is greatly appreciated.

Posting Permissions

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