Cache Procedure with multiple inserts
I am trying to create a Procedure in Caché, but this message is showing:
<UNDEFINED>frmit+118^%qaqpsq *mt("v",1)
This is the procedure:
CREATE PROCEDURE testebi.sp_cargainicial()
BEGIN
INSERT INTO testebi.Fato_Atendimentos (
PK_OsProcedimento )
SELECT ID
from dado.TblOsProcedimento ;
UPDATE testebi.Fato_Atendimentos as A SET PrimeiraOS = 1
FROM
(select min(convert(int,data)) data, paciente
from dado.arqordemservico
group by paciente) as b
WHERE a.fk_paciente = b.paciente and a.fk_Data = b.data;
end
It works if I try to create the procedure with only the INSERT or with UPDATE part like this:
CREATE PROCEDURE testebi.sp_cargainicial()
BEGIN
UPDATE testebi.Fato_Atendimentos as A SET PrimeiraOS = 1
FROM
(select min(convert(int,data)) data, paciente
from dado.arqordemservico
group by paciente) as b
WHERE a.fk_paciente = b.paciente and a.fk_Data = b.data;
end
Have you ever see this error?
Comments
It looks to me as if the query optimizer gets confused to have INSERT and UPDATE to the SAME table in sequence.
If they both run well when separated I'd suggest calling both in sequence.
From code or performance, I see no advantage to pack them together. Rather the opposite.
Not sure which version you are on, but the concept works fine on a recent IRIS kit I tried and as such this might be an older bug. In any case, this kind of errors warrants a call with the WRC.