Question
Rodrigo Mori · Sep 17, 2020

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?

00
0 2 83 0

Replies

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.