As a beginner, I am not able to see any error logically. All I am doing here is saving data into a variable and comparing that in if statements. However, at some point it returns more than requested rows and causes error.
Thanks in advance for your help.
CREATE OR REPLACE PROCEDURE Late_Payment_Days(Inv_ID Varchar2)
AS
Payment_Exist Number;
Late_Payment Number;
BEGIN
DBMS_OUTPUT.Enable();
SELECT Payment_Total
INTO Payment_Exist
FROM Invoices Where Invoice_ID = Inv_ID;
SELECT
( (SELECT PAYMENT_DATE FROM INVOICES WHERE INVOICE_ID = Inv_ID)
- (SELECT INVOICE_DUE_DATE FROM INVOICES WHERE INVOICE_ID= Inv_ID)
)
INTO Late_Payment
FROM Invoices;
IF Payment_Exist=0 THEN
DBMS_OUTPUT.PUT_LINE('No Payment!');
IF Late_Payment > 0 THEN
DBMS_OUTPUT.PUT_LINE(Late_Payment || 'Overdue Days');
ELSE
DBMS_OUTPUT.PUT_LINE('Zero overdue days');
END IF;
END IF;
End;
/
Show Error;
EXEC Late_Payment_Days(5);
Best Answer
I think that this part is responsible for the error;
If there is more than 1 row in
Invoices
, the result of the query will be many rows (with the same value) and we can only insert one value into the variableLate_Payment
.You can change the
FROM Invoices
toFROM dual
(a special Oracle table with a single row):or - since both subqueries use the same
FROM
andWHERE
- simplify to: