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
[CODE=oracle]CREATE OR REPLACE function IssuanceBalance QTY (
Issuance_ID in number
)
return number
IS
sumissuance number;
sumreturn number;
difference number;
begin

-- GET SUM OF ISSUANCE QUANTITY ----
select sum(WT_BD_ISSUE DETAIL.QUANTITY ) into sumissuance
from wt_bd_issuedeta il
where wt_bd_issuedeta il.ISSUANCE_ID = Issuance_ID;

-- GET SUM OF RETURN QUANTITY --
select sum(WT_BARDANAR ETURNDT.QUANTIT Y) into sumreturn
from WT_BARDANARETUR NDT
where wt_bardanaretur ndt.RETURNID= (select wt_bardanaretur n.RETURNID from WT_BARDANARETUR N where wt_bardanaretur n.ISSUANCE_ID = Issuance_ID);

-- CALCULATE DIFFERANCE --
difference := (sumissuance - sumreturn);
return difference;
end;[/CODE]

[amitpatel66]

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

[CODE=oracle]select sum(WT_BARDANAR ETURNDT.QUANTIT Y) into sumreturn
from WT_BARDANARETUR NDT
where wt_bardanaretur ndt.RETURNID IN (select wt_bardanaretur n.RETURNID from WT_BARDANARETUR N where wt_bardanaretur n.ISSUANCE_ID = Issuance_ID);[/CODE]
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_BARDANARETUR NDT and WT_BARDANARETUR N

or

if the table WT_BARDANARETUR N 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