pl/sql cursors [message #37218] |
Thu, 24 January 2002 20:05 |
hnetscape
Messages: 30 Registered: December 2001 Location: India
|
Member |
|
|
Hi,
I have a procedure as below. When Iam using it in sql it is giving the correct result as below. But when i use the same in d2k reports it is giving result only the first row and remaining rows it is not showing. Can some one help me in getting the same result as in at sql prompt.
declare
cursor ao2 is
select compcode,branchcd,seriescd,aono,slno,deliveryqty,deliverydt
from saao2
where compcode='02'
and branchcd= 'A1'
and seriescd= 'AO'
and aono= '20010390'
order by compcode,branchcd,seriescd,aono,slno;
Begin
for ao2rec in ao2 loop
dbms_output.put_line(ao2rec.deliveryqty);
exit when ao2%notfound;
end loop;
End;
sql> /
15
12
15
10
15
30
56
97
Thanks.
Raju.
|
|
|
|
Re: pl/sql cursors [message #37249 is a reply to message #37218] |
Sun, 27 January 2002 19:52 |
hnetscape
Messages: 30 Registered: December 2001 Location: India
|
Member |
|
|
hi,
following is the code in which iam getting only one value but i want to have all the row result.
function CF_1Formula return Number is
vcocd char(2);
vbrcd char(2);
vscd char(3);
vaono char(8);
vslno number(3);
vdqty number(9);
vddt date;
cursor ao2 is
select compcode,branchcd,seriescd,aono,slno,deliveryqty,deliverydt
from saao2
where compcode='02'
and branchcd= 'A1'
and seriescd= 'AO'
and aono= '20010390'
order by compcode,branchcd,seriescd,aono,slno;
Begin
open ao2;
loop
fetch ao2 into vcocd,vbrcd,vscd,vaono,vslno,vdqty,vddt;
return(vdqty);
exit when ao2%notfound;
end loop;
close ao2;
End;
thanks.
Raju.
|
|
|
Re: pl/sql cursors [message #37258 is a reply to message #37218] |
Mon, 28 January 2002 05:26 |
Suresh Vemulapalli
Messages: 624 Registered: August 2000
|
Senior Member |
|
|
formula trigger in reports executes one time for each row. i think, you have to modify select statement in function by passing current row (other column values) values.
|
|
|