Re: How to write signed numeric?

From: Brian <bgoldber_at_mcare.med.umich.edu>
Date: 24 Mar 2004 10:56:36 -0800
Message-ID: <e79c50f9.0403241056.64c421e4_at_posting.google.com>

/*****************************************************************************

* overpunch.fnc
*

• Description: Takes the number p_number and converts it to signed overpunch
• in COBOL style PIC S9(p_length) by returning a varchar value
• of length p_length.
  *
  
• Tables Accessed: None
  *
  
• Parameters:
  *
  
• Name Type Description
• p_number NUMBER value of the number to convert to overpunch
• format.
• p_length NUMBER length of the overpunched return value
  *
  
• Returns: VARCHAR2
  *
  
• Error codes: p_length can only be between the values 3 and 6.
  *
  
• Calls: None
  *
  
• Created By: Brian Goldberg
• Date Created: 03/24/2004
• Project: 11553
• Note: Initial creation.
  *
  *****************************************************************************/
  CREATE OR REPLACE FUNCTION overpunch ( p_number IN NUMBER, p_length IN NUMBER) RETURN VARCHAR2 IS retval VARCHAR2(10); l_signed_value VARCHAR(10); l_p_length_out_range EXCEPTION; l_body VARCHAR(10); l_sign_end VARCHAR(10); l_last_digit VARCHAR(1); BEGIN IF p_length = 3 THEN l_signed_value := TO_CHAR(p_number, 'S000'); ELSIF p_length = 4 THEN l_signed_value := TO_CHAR(p_number, 'S0000'); ELSIF p_length = 5 THEN l_signed_value := TO_CHAR(p_number, 'S00000'); ELSIF p_length = 6 THEN l_signed_value := TO_CHAR(p_number, 'S000000'); ELSE RAISE l_p_length_out_range; END IF; -- p_length
  • l_body is the number without the last digit
  • l_sign_end is the sign and the last digit

   l_body := SUBSTR(l_signed_value, 2, p_length - 1);

   l_sign_end := SUBSTR(l_signed_value, 1, 1) || 
                 SUBSTR(l_signed_value, p_length + 1, 1);
   SELECT DECODE(l_sign_end, '+0', '{', '+1', 'A', '+2', 'B', '+3', 'C',
                  '+4', 'D', '+5', 'E', '+6', 'F', '+7', 'G', '+8', 'H', 
                  '+9', 'I', '-0', '}', '-1', 'J', '-2', 'K', '-3', 'L', 
                  '-4', 'M', '-5', 'N', '-6', 'O', '-7', 'P', '-8', 'Q', 
                  '-9', 'R') INTO l_last_digit FROM DUAL;

   retval := l_body || l_last_digit;
   RETURN retval;
EXCEPTION
  WHEN l_p_length_out_range THEN
    DBMS_OUTPUT.PUT_LINE('The length of the overpunch function is out of ' ||

                         'bounds');

  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('overpunch.fnc has an error: ' || SQLERRM); END;
/
EXIT;
Steve Butler <sbut-is_at_seatimes.com> wrote in message news:<Pine.SUN.3.91.950407084852.26428A-100000_at_seatimes>...

> On Thu, 6 Apr 1995, Spencer H Moore wrote:
> > mainframe system.  Several of the fields are "signed numeric".  I believe this 
> > means that the last byte of the field is an alphanumeric character such as 
> > "A",  "J", "}", etc., depending upon the sign and value.
> > 
> > Does anyone have an algorithm to convert a number to a "signed numeric" 
> > string in PL/SQL?  I searched the FAQ and couldn't find anything (probably 
> 
> You will have better luck fixing things up on the COBOL side.  COBOL 
> allows the use of SIGN IS SEPERATE [LEADING/TRAILING] so that the fields 
> can be signed without having the sign overpunch the last digit [harking 
> back to punched card terminology].
> 
> --Steve
> 
> +----------------------------------------------------+
> | Steve Butler          Voice:  206-464-2998         |
> | The Seattle Times       Fax:  206-382-8898         |
> | PO Box 70          Internet:  sbut-is_at_seatimes.com |
> | Seattle, WA 98111    Packet:  KG7JE_at_N6EQZ.WA       |
> +----------------------------------------------------+
> All standard and non-standard disclaimers apply.
> All other sources are annonymous.

Received on Wed Mar 24 2004 - 19:56:36 CET