Home » SQL & PL/SQL » SQL & PL/SQL » Insert with sum statement doesn't work
icon4.gif  Insert with sum statement doesn't work [message #230305] Wed, 11 April 2007 08:48 Go to next message
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 #230314 is a reply to message #230305] Wed, 11 April 2007 09:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64144
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Insert with sum statement doesn't work [message #230324 is a reply to message #230305] Wed, 11 April 2007 09:30 Go to previous messageGo to next message
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 #230327 is a reply to message #230324] Wed, 11 April 2007 09:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64144
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not what I am asking.

[Updated on: Wed, 11 April 2007 09:37]

Report message to a moderator

Re: Insert with sum statement doesn't work [message #230330 is a reply to message #230327] Wed, 11 April 2007 09:45 Go to previous messageGo to next message
binho_sjc
Messages: 8
Registered: April 2007
Junior Member
So, What do you ask? Sorry I dont understand .
Re: Insert with sum statement doesn't work [message #230332 is a reply to message #230330] Wed, 11 April 2007 09:53 Go to previous message
Michel Cadot
Messages: 64144
Registered: March 2007
Location: Nanterre, France, http://...
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).

Previous Topic: query format .
Next Topic: Count row
Goto Forum:
  


Current Time: Fri Dec 09 11:24:57 CST 2016

Total time taken to generate the page: 0.26351 seconds