OraFAQ Forum: SQL & PL/SQL » Insert with sum statement doesn't work

Home » SQL & PL/SQL » SQL & PL/SQL » Insert with sum statement doesn't work

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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 !

Report message to a moderator

Re: Insert with sum statement doesn't work [message #230314 is a reply to message #230305]

Wed, 11 April 2007 09:18

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Do you really execute the insert or just the select part?
Post (copy and paste) your execution (don't forget to use formatting tags).

Regards
Michel

Report message to a moderator

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 ....

Report message to a moderator