4 Replies Latest reply: Apr 16, 2014 7:59 AM by Fernando Tonial RSS

    Incremental load

    Saniffer Magalhaes

      Need to do an incremental load,

       

      MFD:

      LOAD

          $(Emp) & '-' & ZS_FILIAL&ZS_DATA&ZS_IMPRESS AS %linkMFD,

          ZS_FILIAL as CodFilialMFD,

          ZS_ALIQICM as AliqMFD,

          ZS_CUPOM as CupomMFD,

          MID(ZS_DATA,1,4) AS Ano,

          $(Emp) & '-' & ZS_FILIAL as CodEmpFilZS,

          MONTH(MAKEDATE(MID(ZS_DATA,1,4),MID(ZS_DATA,5,2))) AS Mes,

        MID(ZS_DATA,7,2) AS Dia,

        date(MAKEDATE(MID(ZS_DATA,1,4),MID(ZS_DATA,5,2),MID(ZS_DATA,7,2))) AS Data,

        weekday(MAKEDATE(MID(ZS_DATA,1,4),MID(ZS_DATA,5,2),MID(ZS_DATA,7,2))) AS DiaSemana,

        ceil(MONTH(MAKEDATE(MID(ZS_DATA,1,4),MID(ZS_DATA,5,2))),4) AS Trimestre,

        MonthName( MakeDate(MID(ZS_DATA,1,4),MID(ZS_DATA,5,2),MID(ZS_DATA,7,2))) AS MesAno,

          ZS_ITEM AS ItemMFD,

          ZS_PDV  as PdvMFD,

          ZS_IMPRESS as ImpressMFD,

          ZS_PRODUTO AS DescProdutoMFD,

          ZS_QTDADE AS QuantidadeMFD,

          ZS_STATUS AS StatusMFD,

          ZS_TIPO   AS TipoMFD,

          ZS_VLRBASE as ValBaseIcmMFD,

          ZS_VLRICMS as ValorIcmMFD,

          ZS_VLRTOTA AS TotalMFD,

          ZS_VLRDESC AS ValorDescMFD,

          ZS_VLRUNIT AS ValorUniMFD;

      SQL SELECT ZS_ALIQICM,

          ZS_CODPROD,

          ZS_CUPOM,

          ZS_DATA,

          ZS_FILIAL,

          ZS_IMPRESS,

          ZS_ITEM,

          ZS_PDV,

          ZS_PRODUTO,

          ZS_QTDADE,

          ZS_STATUS,

          ZS_TIPO,

          ZS_VLRBASE,

          ZS_VLRICMS,

          ZS_VLRTOTA,

          ZS_VLRDESC,

          ZS_VLRUNIT

      //FROM SigaProdp10.dbo.SZS010;

      FROM SZS$(Emp)0 SZS

      WHERE

        D_E_L_E_T_ <> '*'

        AND ZS_DATA >= '20130401'

        AND ZS_DATA <='20131231';

       

       

      STORE * FROM MFD into MFD$(Emp).QVD (qvd);

      DROP Table MFD;

        • Re: Incremental load
          Magnus ÅVITSLAND

          Hi Saniffer.

          Have you tried lurking the forums?

          • Re: Incremental load
            Fernando Tonial

            Hi, try this.

             

            Check the code:

             

            LET vDataInicio = Date(YearStart(Today()),'YYYYMMDD');
            LET vDataFim = Date(Today(),'YYYYMMDD');
            
            MFD:
            LOAD 
                $(Emp) & '-' & ZS_FILIAL&ZS_DATA&ZS_IMPRESS AS %linkMFD,
                ZS_FILIAL as CodFilialMFD,
                ZS_ALIQICM as AliqMFD,
                ZS_CUPOM as CupomMFD,
                MID(ZS_DATA,1,4) AS Ano,
                $(Emp) & '-' & ZS_FILIAL as CodEmpFilZS,
                MONTH(MAKEDATE(MID(ZS_DATA,1,4),MID(ZS_DATA,5,2))) AS Mes,
              MID(ZS_DATA,7,2) AS Dia,
              date(MAKEDATE(MID(ZS_DATA,1,4),MID(ZS_DATA,5,2),MID(ZS_DATA,7,2))) AS Data,
              weekday(MAKEDATE(MID(ZS_DATA,1,4),MID(ZS_DATA,5,2),MID(ZS_DATA,7,2))) AS DiaSemana,
              ceil(MONTH(MAKEDATE(MID(ZS_DATA,1,4),MID(ZS_DATA,5,2))),4) AS Trimestre,
              MonthName( MakeDate(MID(ZS_DATA,1,4),MID(ZS_DATA,5,2),MID(ZS_DATA,7,2))) AS MesAno,
                ZS_ITEM AS ItemMFD,
                ZS_PDV  as PdvMFD,
                ZS_IMPRESS as ImpressMFD,
                ZS_PRODUTO AS DescProdutoMFD,
                ZS_QTDADE AS QuantidadeMFD,
                ZS_STATUS AS StatusMFD,
                ZS_TIPO   AS TipoMFD,
                ZS_VLRBASE as ValBaseIcmMFD,
                ZS_VLRICMS as ValorIcmMFD,
                ZS_VLRTOTA AS TotalMFD,
                ZS_VLRDESC AS ValorDescMFD,
                ZS_VLRUNIT AS ValorUniMFD;
            SQL SELECT ZS_ALIQICM,
                ZS_CODPROD,
                ZS_CUPOM,
                ZS_DATA,
                ZS_FILIAL,
                ZS_IMPRESS,
                ZS_ITEM,
                ZS_PDV,
                ZS_PRODUTO,
                ZS_QTDADE,
                ZS_STATUS,
                ZS_TIPO,
                ZS_VLRBASE,
                ZS_VLRICMS,
                ZS_VLRTOTA,
                ZS_VLRDESC,
                ZS_VLRUNIT
            //FROM SigaProdp10.dbo.SZS010;
            FROM SZS$(Emp)0 SZS
            WHERE
              D_E_L_E_T_ <> '*' 
              AND ZS_DATA >= '$(vDataInicio)'
              AND ZS_DATA <='$(vDataFim)';
            
            LOAD * FROM MFD$(Emp).QVD (qvd);
            
            STORE * FROM MFD into MFD$(Emp).QVD (qvd);
            DROP Table MFD;
            

             

            Best Regards.

            Tonial.