Home » SQL & PL/SQL » SQL & PL/SQL » exact fetch returns more than requested number of rows
exact fetch returns more than requested number of rows [message #274136] Sun, 14 October 2007 08:30 Go to next message
preetinagrawal
Messages: 1
Registered: October 2007
Junior Member
create or replace procedure net_sal_cal(e_id number,fromdate date,todate date,month number,year number)
is
grosssal number(8);
to_hrs varchar2(10);
v_days number(3);
v_count number(3);
v_leaves number(3);
mon_hrs number(4);
begin
select gross into grosssal from gross_sal where e_id=e_id;
select decode(sum(total_hrs),Null,0) into to_hrs from timecard where emp_id=e_id and (date_detail between fromdate and todate);
select days into v_days from workdays where month=month;
select count(leave) into v_count from timecard where emp_id=e_id and (leave between fromdate and todate);
select leaves into v_leaves from employee where e_id=e_id;
if v_count > 0 then
if v_leaves > 0 then
to_hrs:=to_hrs + (v_leaves * 8);
end if;
end if;
mon_hrs:=v_days*8;
if (to_hrs = mon_hrs) then
insert into net_sal values(e_id,200,500,grosssal-700,month,year);
end if;
end;
/
When i am truing to execute this proc. i m getting error as gross sal n all other tables contains only one row of particular emp id.
The error is: exact fetch returns more than requested number of rows
Re: exact fetch returns more than requested number of rows [message #274137 is a reply to message #274136] Sun, 14 October 2007 09:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Use SQL*Plus and COPY AND PASTE what you have, don't describe it.

Regards
Michel
Re: exact fetch returns more than requested number of rows [message #274147 is a reply to message #274136] Sun, 14 October 2007 11:41 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One record for a particular EMP_ID? Fine. What about
select days into v_days from workdays where month=month;
Is there only one working day for every month?
Re: exact fetch returns more than requested number of rows [message #274160 is a reply to message #274136] Sun, 14 October 2007 18:14 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
your problem lies here, I would imagine as well as littlefoot's spot (and based on this, I'll vouch that you have other problems too.

select gross into grosssal from gross_sal where e_id=e_id;

but as Michel says. Please read (and adhere to) the guidelines before posting

Here too

select leaves into v_leaves from employee where e_id=e_id;

Think up better names for your parameters

[Updated on: Sun, 14 October 2007 18:17]

Report message to a moderator

Previous Topic: can someone suggest a select for this
Next Topic: Premature EOF encountered, no trailer found
Goto Forum:
  


Current Time: Sun Dec 04 10:27:09 CST 2016

Total time taken to generate the page: 0.04671 seconds