Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

changing a zero value to 'N/A'

584851Jan 4 2008 — edited Jan 4 2008
i want to create a function that returns a varchar2, however if the value is 0 i want to display 'N/A'. Here is my code but i'm sure there's a better way to do it and plus i'm getting ORA-06502: PL/SQL: numeric or value error on V_GEN := TO_NUMBER(V_GEN, V_FORMAT_#)

the purpose of this is to create 3 functions where CALC_SUM = CALC_1 + CALC_2 but if a zero exists then display N/A


FUNCTION CALC_1 (V1 IN NUMBER) RETURN VARCHAR2
IS
V_SUM NUMBER := 0;
V_AMOUNT NUMBER := 0;
V_ZERO VARCHAR2(10) := 'N/A';
BEGIN
FOR CURS IN (SELECT ANNUAL_AMOUNT, PERIOD FROM TABLE)
LOOP
V_AMOUNT := CURS.ANNUAL_AMOUNT / 12 * CURS.PERIOD;
V_SUM := V_SUM + V_AMOUNT;
END LOOP;

IF V_SUM = 0 THEN
RETURN V_ZERO;
END IF;

RETURN TO_CHAR(V_SUM, 999,999,990.99);
END CALC_1;



FUNCTION CALC_2 (V2 IN NUMBER) RETURN VARCHAR2
IS
V_SUM NUMBER := 0;
V_AMOUNT NUMBER := 0;
V_ZERO VARCHAR2(10) := 'N/A';
BEGIN
FOR CURS IN (SELECT ANNUAL_AMOUNT, PERIOD FROM TABLE)
LOOP
V_AMOUNT := CURS.ANNUAL_AMOUNT / 12 * CURS.PERIOD;
V_SUM := V_SUM + V_AMOUNT;
END LOOP;

IF V_SUM = 0 THEN
RETURN V_ZERO;
END IF;

RETURN TO_CHAR(V_SUM, 999,999,990.99);
END CALC_2;



FUNCTION CALC_SUM (V IN NUMBER) RETURN VARCHAR2
IS
V_GEN VARCHAR2(100) := Pkg.CALC_1(V1);
V_CUST VARCHAR2(100) := Pkg.CALC_2(V2);
V_TOTAL NUMBER;
V_ZERO VARCHAR2(10) := 'N/A';
BEGIN
IF V_GEN = 'N/A' THEN
V_GEN := 0;
END IF;
IF V_CUST = 'N/A' THEN
V_CUST := 0;
END IF;
IF V_GEN = 'N/A' AND V_CUST = 'N/A' THEN
RETURN V_ZERO;
END IF;
V_GEN := TO_NUMBER(V_GEN, 999,999,990.99);
V_CUST := TO_NUMBER(V_CUST, 999,999,990.99);
V_TOTAL := V_GEN + V_CUST;
IF V_TOTAL = 0 THEN
RETURN V_ZERO;
ELSE
RETURN TO_CHAR(V_TOTAL, 999,999,990.99);
END IF;
END CALC_SUM;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 1 2008
Added on Jan 4 2008
38 comments
1,843 views