Home » SQL & PL/SQL » SQL & PL/SQL » Insert with sum statement doesn't work
Insert with sum statement doesn't work [message #230305] |
Wed, 11 April 2007 08:48 |
binho_sjc
Messages: 8 Registered: April 2007
|
Junior Member |
|
|
Im from Brazil, so Im sorry about my english. I have a PLSQL procedure with this insert :
insert into sam.tbrelequipes
select a.dsequipe,null,a.dslocal,null,'TOTAIS',
trim(to_char(sum(to_number(a.hrdisp,'99999.99')),'99990.99')),
trim(to_char(sum(to_number(a.hrosm,'99999.99')),'99990.99')),
trim(to_char(sum(to_number(a.hrdo,'99999.99')),'99990.99')),
trim(to_char(sum(to_number(a.hrtotais,'99999.99')),'99990.99')),
trim(to_char(((sum(to_number(a.hrtotais,'99999.99'))/sum(to_number(a.hrdisp,'99999.99')))*100),'99990.99'))
from sam.tbrelequipes a
group by a.dsequipe,a.dslocal;
This insert is at the end of that Procedure.
When I run the procedure , this insert works like a recursive insert and I get an error saying that Im inserting a value greater than field. But when I copy and paste this same insert on a sql window it works like I was expecting .. Any idea?
Thank you !
|
|
|
|
Re: Insert with sum statement doesn't work [message #230324 is a reply to message #230305] |
Wed, 11 April 2007 09:30 |
binho_sjc
Messages: 8 Registered: April 2007
|
Junior Member |
|
|
This is my complete procedure
CREATE OR REPLACE Procedure ProcPreencheTbRelequipes
(
pdatainic in varchar2,
pdatafim in varchar2,
pcdequipe in sam.tbequipe.cdequipe%type,
pcdlocal in sam.tblocal.cdlocal%type
)
is
i number;
diasuteis number;
dia date;
--cursor cequipe: traz a equipe que foi passada ou todas as equipes
cursor cequipe is select cdequipe,dsequipe
from sam.tbequipe
where cdequipe = pcdequipe;
registro1 cequipe%rowtype;
--cursor cmembros: traz os membros de cada equipe
cursor cmembros (codequipe varchar2)
is select a.cdmatric,b.nmprofiss,c.dsespec
from sam.tbmembrosequipe a,sam.tbprofissional b,sam.tbespecialidade c
where a.cdequipe = codequipe
and a.cdmatric = b.cdmatric
and b.cdespec = c.cdespec;
registro2 cmembros%rowtype;
--cursor clocal: traz os locais
cursor clocal is select a.cdlocal,a.tplocal||' '||a.dslocal as desclocal
from sam.tblocal a
where a.cdlocal = pcdlocal;
registro3 clocal%rowtype;
-- cursor cosm: traz o total de horas gastas em OSM's
cursor cosm (codlocal varchar2,codmatric varchar2,dtinicio varchar2,dtfim varchar2)
is select nvl(sum(round(((c.hrfim-c.hrinicio)*24),2)),0) as totalosm,nvl(trunc((sum(c.hrfim-c.hrinicio))*24*60),0) as minutos
FROM sam.tbprofissional a,sam.tbespecialidade b,sam.tbosmprof c,sam.tblocal f,sam.tbosm g
WHERE a.cdespec = b.cdespec
and a.cdmatric = c.cdmatric
and c.cdosm = g.cdosm
and g.cdlocal = f.cdlocal
and c.dtexecucao between dtinicio and dtfim
and c.cdmatric = codmatric
and g.cdlocal = codlocal;
registro4 cosm%rowtype;
-- cursor cdo: traz o total de horas gastas em DO's
cursor cdo (codlocal varchar2,codmatric varchar2,dtinicio varchar2,dtfim varchar2)
is select nvl(sum(round(((c.hrfim-c.hrinicio)*24),2)),0) as totaldo,nvl(trunc((sum(c.hrfim-c.hrinicio))*24*60),0) as minutos
FROM sam.tbprofissional a,sam.tbespecialidade b,sam.tbdoprof c,sam.tblocal f,sam.tbdo g
WHERE a.cdespec = b.cdespec
and a.cdmatric = c.cdmatric
and c.cddo = g.cddo
and g.cdlocal = f.cdlocal
and c.dtexecucao between dtinicio and dtfim
and c.cdmatric = codmatric
and g.cdlocal = codlocal;
registro5 cdo%rowtype;
-- cursor chorasdisp: traz o total de horas disponiveis no periodo
cursor chorasdisp (diasdisp number,codmatric varchar2)
is select
nvl(sum((to_number(substr(to_char(a.qtjornada,'HH24:MI'),1,2))*60+to_number(substr(to_char(a.qtjornada,'HH24:MI'),4,2)))*diasdisp)/60 ,0) as totaldisp,
nvl(sum((to_number(substr(to_char(a.qtjornada,'HH24:MI'),1,2))*60+to_number(substr(to_char(a.qtjornada,'HH24:MI'),4,2)))*diasdisp),0) as minutos
FROM SAM.TBPROFISSIONAL A
WHERE A.Cdmatric = codmatric;
registro6 chorasdisp%rowtype;
begin
DBMS_SESSION.SET_NLS('NLS_DATE_LANGUAGE','PORTUGUESE');
diasuteis := 0;
Dia := to_date(pdatainic,'DD/MM/YYYY');
loop
exit when Dia > to_date(pdatafim,'DD/MM/YYYY');
if next_day(Dia-1,'SABADO') <> Dia then
if next_day(Dia-1,'DOMINGO') <> Dia then
diasuteis := diasuteis + 1;
end if;
end if;
Dia := Dia + 1;
end loop;
Commit;
open cequipe;
loop
fetch cequipe into registro1; -- lê a equipe
exit when cequipe%notfound;
open cmembros(registro1.cdequipe);
loop
fetch cmembros into registro2; -- lê os membros da equipe
exit when cmembros%notfound;
open clocal;
loop
fetch clocal into registro3; -- lê os locais que foram selecionados
exit when clocal%notfound;
open chorasdisp(diasuteis,registro2.cdmatric);
fetch chorasdisp into registro6;
open cosm(registro3.cdlocal,registro2.cdmatric,pdatainic,pdatafim);
fetch cosm into registro4;
open cdo(registro3.cdlocal,registro2.cdmatric,pdatainic,pdatafim);
fetch cdo into registro5;
insert into tbrelequipes
values (registro1.dsequipe,
registro2.cdmatric,
registro3.desclocal,
registro2.nmprofiss,
registro2.dsespec,
to_char(nvl(registro6.totaldisp,0),'990.99'),
to_char(nvl(registro4.totalosm,0),'990.99'),
to_char(nvl(registro5.totaldo,0),'990.99'),
to_char(nvl(registro4.totalosm,0)+nvl(registro5.totaldo,0),'990.99'),
to_char(((nvl(registro4.totalosm,0)+nvl(registro5.totaldo,0))/nvl(registro6.totaldisp,0))*100,'990.99'));
commit;
close cdo;
close cosm;
close chorasdisp;
end loop;
close clocal;
end loop;
close cmembros;
end loop;
close cequipe;
delete from sam.tbrelequipes b -- apagar os locais que não tiveram nenhum atendimento pela equipe
where b.dsequipe||' '||b.dslocal
in (select a.dsequipe||' '||a.dslocal
from sam.tbrelequipes a
having sum(to_number(a.hrtotais,'999,99')) = 0
group by a.dsequipe,a.dslocal);
commit;
insert into sam.tbrelequipes
select a.dsequipe,null,a.dslocal,null,'TOTAIS',trim(to_char(sum(to_number(a.hrdisp,'99999.99')),'99990.99')),trim(to_char(sum(to_number(a.hr osm,'99999.99')),'99990.99')),trim(to_char(sum(to_number(a.hrdo,'99999.99')),'99990.99')),trim(to_char(sum(to_number(a.hrtotais,'9999 9.99')),'99990.99')),trim(to_char(((sum(to_number(a.hrtotais,'99999.99'))/sum(to_number(a.hrdisp,'99999.99')))*100),'99990.99'))
from sam.tbrelequipes a
group by a.dsequipe,a.dslocal;
commit;
end;
The insert is on the end of procedure ....
|
|
|
|
|
Re: Insert with sum statement doesn't work [message #230332 is a reply to message #230330] |
Wed, 11 April 2007 09:53 |
|
Michel Cadot
Messages: 68722 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
You said:
Quote: | But when I copy and paste this same insert on a sql window it works like I was expecting
|
So I asked:
Quote: | Post (copy and paste) your execution (don't forget to use formatting tags).
|
|
|
|
Goto Forum:
Current Time: Thu Dec 12 08:22:41 CST 2024
|