Procedure y Tablas Temporales

[Abigail913]

Gracias por responder gnzsoloyo;

Si bien no es sql, necesito investigar y crear una idea para este caso,

este si es el codigo original;


CREATE PROCEDURE "informix".c_actv_dias18034(i_id_grupo INTEGER,i_t_actv INTEGER, i_dia INTEGER) RETURNING INTEGER;
DEFINE i_cant INTEGER;
DEFINE i_dur INTERVAL DAY TO DAY;
DEFINE i_t_actv2 INTEGER;

SELECT * FROM ACTIVIDAD WHERE fe_fin IS NULL
INTO TEMP ACT0;

SELECT * FROM TIPO_ACTIVIDAD WHERE t_actv IN (SELECT t_actv FROM ACT0)
INTO TEMP TIPO_ACT0;

SELECT * FROM HIST_ASIGNACION WHERE id_actv IN (SELECT id_actv FROM ACT0)
INTO TEMP HIST_ASIGN0;

IF (i_dia < 5) THEN
SELECT COUNT(*),a.t_actv,(EXTEND(CURRENT,YEAR TO DAY) - EXTEND(a.fe_ini,YEAR TO DAY))
INTO i_cant,i_t_actv2,i_dur
FROM ACT0 a, TIPO_ACT0 ta, HIST_ASIGN0 h, usuario u, personal p
WHERE a.fe_fin IS NULL
AND ta.t_Actv=a.t_Actv
AND ta.t_actv=i_t_actv
AND h.id_actv=a.id_actv
AND u.nu_carn=p.nu_carn
AND h.id_usr=u.id_usr
AND p.id_grupo_asg=i_id_grupo
AND (EXTEND(CURRENT,YEAR TO DAY) - EXTEND(a.fe_ini,YEAR TO DAY)) >= i_dia UNITS DAY
AND (EXTEND(CURRENT,YEAR TO DAY) - EXTEND(a.fe_ini,YEAR TO DAY)) < (i_dia+1) UNITS DAY
GROUP BY 2,3;
ELSE
SELECT COUNT(*),a.t_actv
INTO i_cant,i_t_actv2
FROM ACT0 a, TIPO_ACT0 ta, HIST_ASIGN0 h, usuario u, personal p
WHERE a.fe_fin IS NULL
AND ta.t_Actv=a.t_Actv
AND ta.t_actv=i_t_actv
AND h.id_actv=a.id_actv
AND u.nu_carn=p.nu_carn
AND h.id_usr=u.id_usr
AND p.id_grupo_asg=i_id_grupo
AND (EXTEND(CURRENT,YEAR TO DAY) - EXTEND(a.fe_ini,YEAR TO DAY)) >= i_dia UNITS DAY
GROUP BY 2;
END IF;

IF i_cant IS NULL THEN
LET i_cant=0;
END IF;
RETURN i_cant;

DROP TABLE ACT0;
DROP TABLE TIPO_ACT0;
DROP TABLE HIST_ASIGN0;

END PROCEDURE;