Good Afternoon Community,
I'm Using Oracle DB 12c
I'm trying to use To_Char to Convert Number to Char to add Formatting But I Can Not
Figure out Character to Number Conversion Error on Line 24 Commented and Highlighted in Red Below
-------------------------------------------------------------------------------------------------------------------
Error report -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 24
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.
------------------------------------------------------------------------------------------------------------------
DECLARE
v_subtotal number;
v_original_subtotal number;
v_P10_RECPT_NMBR1 VARCHAR2(25) := '9000000294';
v_P10_RECPT_NMBR2 VARCHAR2(25) := '9000000059';
v_P10_SubTotal Number(11,2) := 3111.59;
v_SubTotal_Error char(1) := 'T';
BEGIN
select Abs(Nvl(Sum(subtotal),0)) into v_subtotal
from receiptdetail
where refunded_recpt_nmbr = v_P10_RECPT_NMBR1;
select Abs(nvl(subtotal,0)) into v_original_subtotal
from receiptdetail
where receiptnumber = v_P10_RECPT_NMBR2;
--CHECK FOR POSITIVE NUMBERS -- REFUNDS HAVE TO BE ENTERED WITH NEGATIVE AMOUNT----P10_SUBTOTAL,'\,',','') > 0
IF TO_CHAR(v_P10_SubTotal, '99,999.99') > '0'
THEN v_SubTotal_Error := 'X';
--check for excessive refund amounts
ElsIF
----------Error Lies here on Line 24 ((ABS(To_Char(v_P10_SubTotal,'99,999.99')) + to_char(v_subtotal,'99,999.99')) > to_char(v_original_subtotal, '99,999.99'))
Then v_SubTotal_Error := 'Y';
End If;
Dbms_Output.put_line(v_subtotal);
Dbms_Output.put_line(v_original_subtotal);
Dbms_Output.put_line(v_SubTotal_Error);
Dbms_Output.put_line(v_P10_RECPT_NMBR1);
Dbms_Output.put_line(v_P10_RECPT_NMBR2);
Dbms_Output.put_line(v_P10_SubTotal);
END;
Thank You in Regards,
DSteele41