returns more than requested number of rows

[orareddy]

hi all.

Sample Record Set.

sal_ sal_ sal_
stmt_id enty_id stmt_iss_dt
121764 50595 8/15/2001
121722 50595 8/15/2001
120521 50595 8/15/2001
120476 50595 7/16/2001
119385 50595 7/16/2001
119338 50595 6/15/2001
118231 50595 6/15/2001



here is SQL script and the error details.
could you pl. let me know how to fix the following script.

DECLARE



v_amt NUMBER;
v_id NUMBER;

CURSOR c1 IS
SELECT MAX(sal_stmt_iss_dt) sal_dt,sal.sal_enty_id
FROM sales_statement sal
GROUP BY sal.sal_ENTY_ID;


BEGIN

FOR r1 IN c1 LOOP
-- dbms_output.put_line(r1.sal_enty_id||' '||r1.sal_dt);
SELECT sal_stmt_drv_bal_amt,sal_stmt_id
INTO v_amt,v_id
FROM sales_Statement ag
WHERE ag.sal_enty_id = r1.sal_enty_id
AND ag.sal_stmt_iss_dt = r1.sal_dt
;


dbms_output.put_line(r1.sal_enty_id||' '||r1.sal_dt);
dbms_output.put_line(v_amt||' '||v_id);

END LOOP;
END;

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 18



thanks in advance.

[mkumarnk]

Hi,

Are u sure that for one
sal_enty_id AND sal_stmt_iss_dt there is only one row.THere is more than one row is returning and u are not handling it through exceptions u can rewrite ad follows

BEGIN

FOR r1 IN c1 LOOP
-- dbms_output.put_line(r1.sal_enty_id||' '||r1.sal_dt);
begin
SELECT sal_stmt_drv_bal_amt,sal_stmt_id
INTO v_amt,v_id
FROM sales_Statement ag
WHERE ag.sal_enty_id = r1.sal_enty_id
AND ag.sal_stmt_iss_dt = r1.sal_dt
exception
when too_many_rows then
dbms_output.put_line(r1.sal_enty_id||' '||r1.sal_dt);
end;
end loop;
end;

[Shestakov]

Oracle did correct error message because:

for example -->
121764 50595 8/15/2001
121722 50595 8/15/2001
120521 50595 8/15/2001

max data = 8/15/2001 and sal_entry_id = 50595 for all rows.

u have to change code of ur programm.
maybe use second cursor maybe something else, depend from business rules of ur application.