/* INICIO CONTROLE DE CARGA INCREMENTAL E INICIAL */ %macro test; PROC SQL; CREATE TABLE TMP_SIAR.W1ZZ34( NUMFUNC NUM FORMAT = 10. INFORMAT = 10. , NUMVINC NUM FORMAT = 9. INFORMAT = 9. , DTINI NUM FORMAT = DATETIME20. INFORMAT = DATETIME20. , DTFIM NUM FORMAT = DATETIME20. INFORMAT = DATETIME20. , FUNCAO NUM FORMAT = 7. INFORMAT = 7. , SETOR CHAR(15) FORMAT = $15. INFORMAT = $15., EMP_CODIGO NUM FORMAT = 3. INFORMAT = 3. , SK_NUM_PROCESSO_JOB NUM); SELECT COUNT(1) AS CONT1 INTO :CONT1 FROM SIAR_CGA.CONTROLE_CARGA_JOBS WHERE COD_UNI_JOB IN (1,3); SELECT COUNT(1) AS TAB1 INTO :TAB1 FROM FAT_SIAR.FAT_PROV_DESIG_SUBST WHERE COD_ORIGEM_TABELA = 1; QUIT; %IF &CONT1=0 %THEN %DO; %IF &TAB1=0 %THEN %DO; PROC SQL PRINT; INSERT INTO TMP_SIAR.W1ZZ34 SELECT ODS_DESIGNACOES_EV.NUMFUNC, ODS_DESIGNACOES_EV.NUMVINC, ODS_DESIGNACOES_EV.DTINI, ODS_DESIGNACOES_EV.DTFIM, ODS_DESIGNACOES_EV.FUNCAO, ODS_DESIGNACOES_EV.SETOR, ODS_DESIGNACOES_EV.EMP_CODIGO, 0 AS SK_NUM_PROCESSO_JOB FROM ODS_SIAR.ODS_DESIGNACOES_EV AS ODS_DESIGNACOES_EV INNER JOIN DIM_SIAR.DIM_VINCULO AS DIM_VINCULO ON ( ODS_DESIGNACOES_EV.NUMFUNC = DIM_VINCULO.NUM_SERVIDOR AND ODS_DESIGNACOES_EV.NUMVINC = DIM_VINCULO.NUM_VINCULO AND ODS_DESIGNACOES_EV.EMP_CODIGO = DIM_VINCULO.COD_EMPRESA ); QUIT; %END; %END; %ELSE %DO; PROC SQL PRINT; INSERT INTO TMP_SIAR.W1ZZ34 SELECT ODS_DESIGNACOES_EV.NUMFUNC, ODS_DESIGNACOES_EV.NUMVINC, ODS_DESIGNACOES_EV.DTINI, ODS_DESIGNACOES_EV.DTFIM, ODS_DESIGNACOES_EV.FUNCAO, ODS_DESIGNACOES_EV.SETOR, ODS_DESIGNACOES_EV.EMP_CODIGO, CONTROLE_CARGA_JOBS.SK_NUM_PROCESSO_JOB FROM ODS_SIAR.ODS_DESIGNACOES_EV AS ODS_DESIGNACOES_EV INNER JOIN DIM_SIAR.DIM_VINCULO AS DIM_VINCULO ON ( ODS_DESIGNACOES_EV.NUMFUNC = DIM_VINCULO.NUM_SERVIDOR AND ODS_DESIGNACOES_EV.NUMVINC = DIM_VINCULO.NUM_VINCULO AND ODS_DESIGNACOES_EV.EMP_CODIGO = DIM_VINCULO.COD_EMPRESA ) INNER JOIN SIAR_CGA.CONTROLE_CARGA_JOBS AS CONTROLE_CARGA_JOBS ON ( CONTROLE_CARGA_JOBS.NUM_SERVIDOR = ODS_DESIGNACOES_EV.NUMFUNC AND CONTROLE_CARGA_JOBS.NUM_VINCULO = ODS_DESIGNACOES_EV.NUMVINC AND CONTROLE_CARGA_JOBS.COD_UNI_JOB IN (1,3) ); QUIT; %END; %mend test;
... View more