single-row subquery return more than one row

[ziaulmurtaza]

the following function show no any error when compiling it but when call this fuction it return the following error
"single-row subquery returns more than one row"
on the second select statement
even it return only one row when i check it indivisually
please check the code and give your kind suggestion.

trully yours

zia

CREATE OR REPLACE function IssuanceBalanceQTY (
Issuance_ID in number
)
return number
IS
sumissuance number;
sumreturn number;
difference number;
begin
 
-- GET SUM OF ISSUANCE QUANTITY ----
   select sum(WT_BD_ISSUEDETAIL.QUANTITY) into sumissuance
   from wt_bd_issuedetail
   where wt_bd_issuedetail.ISSUANCE_ID = Issuance_ID;
 
-- GET SUM OF RETURN QUANTITY --
   select sum(WT_BARDANARETURNDT.QUANTITY) into sumreturn
   from  WT_BARDANARETURNDT
   where wt_bardanareturndt.RETURNID= (select wt_bardanareturn.RETURNID from WT_BARDANARETURN where wt_bardanareturn.ISSUANCE_ID = Issuance_ID);
 
-- CALCULATE DIFFERANCE --
   difference := (sumissuance - sumreturn);
   return difference;
end;

[amitpatel66]

Use the below code for second Query and check if it works:

select sum(WT_BARDANARETURNDT.QUANTITY) into sumreturn
from WT_BARDANARETURNDT
where wt_bardanareturndt.RETURNID IN (select wt_bardanareturn.RETURNID from WT_BARDANARETURN where wt_bardanareturn.ISSUANCE_ID = Issuance_ID);

I have used IN instead of '=' sign for RETURNID. This might produce different result. If u expect only one RETURNID to be returned by the inner subquery then check if u r not missing any more JOIN between WT_BARDANARETURNDT and WT_BARDANARETURN

or

if the table WT_BARDANARETURN contains repeated values and u want only one, then use DISTINCT in Inner subquery

[ziaulmurtaza]

thanx mr amitpatel
i did it with IN and got the desired results

yours

zia ul murtaza