| reports [message #80866] |
Mon, 09 December 2002 18:52  |
murli
Messages: 25
Registered: November 2002
|
Junior Member |
|
|
I have written these two functions,My problem it is not returning the values when i give a select statement.please helm me out its urgen????????????????
create or replace function today_volume
return number is
v_netvolume number;
v_dat date;
v_name varchar2(12);
cursor c1 is
select volume,dat,name
from bus
where dat = v_dat;
begin
open c1;
loop
fetch c1 into v_netvolume,v_dat,v_name;
exit when c1%notfound;
end loop;
close c1;
return (v_netvolume);
end today_volume;
create or replace function yesterday_volume
return number is
v_netvolume number;
v_dat date;
v_name varchar2(12);
cursor c1 is
select volume,dat,name
from bus
where dat = v_dat - 1 and
name = v_name;
begin
open c1;
loop
fetch c1 into v_netvolume,v_dat,v_name;
exit when c1%notfound;
end loop;
close c1;
return (v_netvolume);
end yesterday_netvolume;
select id,name,dat,today_volume,yesterday_volume
from bus
where dat = '01-dec-02'
and name = 'Credit'
|
|
|
|
| Re: reports [message #80924 is a reply to message #80866] |
Sat, 14 December 2002 08:29  |
Piyush Balan
Messages: 26
Registered: December 2002
|
Junior Member |
|
|
Hi,
after having a quick look at ur code, i can summarize the basic problem.
u have defined ur variables without putting any initial values . and these very same variables are used in conditions of the queries of ur cursor. so obviously ur query conditons are being comapred to NULL values hence no rows are being processed in the cursors.
ideally define IN Parameters of DATE type for ur functions and use them in the cursors. be sure to use trunc() function in the date comparisons. it should definitely work.
regards,
Piyush Balan,
Consultant
Comsoft Pte Ltd
Singapore
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
