5 Replies Latest reply: Jan 16, 2017 8:24 AM by Eduardo DImperio RSS

    Incremental Update

    Eduardo DImperio

      Hi everyone,

       

      I need to create a qvd only with the new data, the problem is that new data could be some measure of today, or last month or any date.

       

      So i thinked about get the last modified on DB. Someone knows how to do that with QS?

        • Re: Incremental Update
          Sunny Talwar

          So i thinked about get the last modified on DB. Someone knows how to do that with QS?

          You think right about last modified date. But what exactly are you asking?

            • Re: Incremental Update
              Eduardo DImperio

              Hey Sunny !

               

              i.e

               

              I had a meter that was broken for a week, then it was fixed and start to send me measure again, so it will populate the DB with measure of today and the last 7 day, but if i load only date=today, i'll lose data of the last 7 days.

               

              Check this out (little big code), i want to load only data that had some modified. (I dont know with QS has some way to check the last modification)


              CONSUMO_CONDOMINIOS:

              LOAD

                  ADDRESS_BLOCK_UC,

                  ADDRESS_COMPLEMENT_UC,

                  NAME_UC,

                  PENDENCY_COUNT,

                  DESCRIPTION_METER,

                  SERIAL_NUMBER_METER,

                  OID_METER,

                  ID_SYSTEM as OID_SYSTEM,

                  TYPE_METER,

                  GROUP_TYPE_METER,

                  TYPE_UNIT,

                  EXTERNAL_KEY_UC,

                  DATEINI,

                  DATEFIM,

                  [DATEFIM]-[DATEINI] AS PERIODO,

                  VALUE_READ_INI,

                  VALUE_READ_FIM,

                  CORRINI,

                  CORRFIM,

                  LEITURAINI,

                  LEITURAFIM,

                  LEITCORRINI,

                  LEITCORRFIM,

                  CONSUMO;

              SQL SELECT

                     

                      si.address_block_uc address_block_uc

                      ,si.address_complement_uc address_complement_uc

                      ,si.name_uc name_uc

                      ,pendency_count

                      ,si.description_meter description_meter

                      ,si.serial_number_meter serial_number_meter

                      ,si.oid_meter oid_meter

                      ,si.id_system id_system

                      ,si.name_type_meter type_meter

                      ,si.group_type_meter group_type_meter

                      ,si.type_unit type_unit

                      ,si.external_key_uc

                      ,date_read_ini dateini

                      ,date_read_fim datefim

                      ,value_read_ini value_read_ini

                      ,value_read_fim value_read_fim

                      ,corr_factor_ini corrini

                      ,corr_factor_fim corrfim

                      ,leitura_ini leituraini

                      ,leitura_fim leiturafim

                      ,leitura_ini + corr_factor_ini leitcorrini

                      ,leitura_fim + corr_factor_fim leitcorrfim

                      ,(leitura_fim + corr_factor_fim) - (leitura_ini + corr_factor_ini) consumo

                  from (select u.address_block_uc, u.address_complement_uc, u.name_uc, u.pendency_count

                              ,u.external_key_uc, m.description_meter, m.serial_number_meter

                              ,tm.name_type_meter,tm.group_type_meter,m.type_unit

                              ,m.oid_meter, lst.id_system

                              ,lst.min_date_read date_read_ini

                              ,ini.value_read value_read_ini

                              ,lst.max_date_read date_read_fim

                              ,fim.value_read value_read_fim

                              ,get_correction_factor( lst.id_system,m.oid_meter,m.oid_uc,lst.min_date_read ) + (get_correction_pulse(lst.id_system,cini.concentrator_counters,cini.port_counters,lst.min_date_read)*m.type_constant) corr_factor_ini

                              ,nvl(f.first_measur,0) + ( ini.value_read * m.type_constant ) leitura_ini

                              ,get_correction_factor( lst.id_system,m.oid_meter,m.oid_uc,lst.max_date_read ) + (get_correction_pulse(lst.id_system,cfim.concentrator_counters,cfim.port_counters,lst.max_date_read)*m.type_constant) corr_factor_fim

                              ,nvl(f.first_measur,0) + ( fim.value_read * m.type_constant ) leitura_fim

                            from (select id_system, m.oid_meter,

                                         min(date_read) min_date_read, max(date_read) max_date_read

                                    from rs_individual_read i, rs_uc u, rs_meter m, rs_counters c

                                    where m.oid_meter = c.oid_meter

                                      and c.concentrator_counters = i.concentrator

                                      and c.port_counters = i.port

                                       and date_read between c.dt_init and c.dt_end

                                      and date_read between m.dt_init and m.dt_end

                                      and u.oid_uc=m.oid_uc

                                      and u.oid_system=i.id_system

              //                         and u.oid_system= 581

                                       and i.date_read between TO_DATE(SYSDATE-3)

                                                               and TO_DATE(SYSDATE+1)

                                  --    and m.individual_flag=?

                                    group by id_system, m.oid_meter

                                 ) lst

                               inner join rs_meter m

                                     on (m.oid_meter = lst.oid_meter

                                         and m.oid_meter = lst.oid_meter

                                         and ( lst.min_date_read between m.dt_init and m.dt_end

                                              or lst.max_date_read between m.dt_init and m.dt_end

                                             )

                                        )

                               inner join rs_counters cini

                                     on (cini.oid_meter=m.oid_meter

                                         and ( lst.min_date_read between cini.dt_init and cini.dt_end )

                                        )

                               inner join rs_counters cfim

                                     on (cfim.oid_meter=m.oid_meter

                                        and ( lst.max_date_read between cfim.dt_init and cfim.dt_end )

                                        )

                               inner join rs_individual_read ini

                                     on (ini.id_system = lst.id_system

                                         and ini.date_read = lst.min_date_read

                                         and ini.concentrator = cini.concentrator_counters

                                         and ini.port = cini.port_counters)

                               inner join rs_individual_read fim

                                     on (fim.id_system = lst.id_system

                                         and fim.date_read = lst.max_date_read

                                         and fim.concentrator = cfim.concentrator_counters

                                         and fim.port = cfim.port_counters)

                               inner join rs_uc u

                                     on (u.oid_system = lst.id_system

                                         and u.oid_uc = m.oid_uc)

                               inner join rs_type_meter tm

                                     on (m.oid_type_meter = tm.oid_type_meter)

                               left outer join rs_first_measurs f

                                     on (f.oid_meter = m.oid_meter)

                       ) si order by address_block_uc,address_complement_uc,name_uc,group_type_meter,type_meter,serial_number_meter;

               

               

              STORE CONSUMO_CONDOMINIOS INTO [lib://MI_csv/CONSUMO_CONDOMINIOS.qvd];

            • Re: Incremental Update
              Eduardo DImperio

              Guys,

               

              I did a test that i believe is the answer, I'll do more tests to be sure about it

               

               

               

              CARGA_INCREMENTAL:

              LOAD

              ID_SYSTEM,

              OID_METER,

              DATE_READ,

              ORA_ROWSCN;

               

               

              select i.id_system,i.ora_rowscn,m.oid_meter,date_read

                                    from rs_individual_read i, rs_uc u, rs_meter m, rs_counters c

                                    where m.oid_meter = c.oid_meter

                                      and c.concentrator_counters = i.concentrator

                                      and c.port_counters = i.port

                                      and date_read between c.dt_init and c.dt_end

                                      and date_read between m.dt_init and m.dt_end

                                      and u.oid_uc=m.oid_uc

                                      and u.oid_system=i.id_system

                                      and i.id_system =20

                                      and i.date_read between TO_DATE(SYSDATE-1)

                                                              and TO_DATE(SYSDATE)

              • Re: Incremental Update
                Eduardo DImperio

                Well,

                 

                It not work like i want.

                 

                If i Try

                 

                CARGA_INCREMENTAL:

                LOAD

                DATA_E_HORA;

                 

                select  id_system,scn_to_timestamp(ora_rowscn) data_e_hora

                from                     

                          rs_individual_read

                 

                I receive that error

                Ocorreram os seguintes erros:

                SQL##f - SqlState: S1000, ErrorCode: 8181, ErrorMsg: [Oracle][ODBC][Ora]ORA-08181: specified number is not a valid system change number ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

                 

                It happen cause scn_to_timestamp get only changes in the last 120 hours, when my select return a ora_rowscn bigger then 120 hours, occur this message. For that reason the first code runs without problem, but no retrieve the right information to me.

                 

                Any Idea?

                • Re: Incremental Update
                  Eduardo DImperio

                  Rá ! I did it !

                   

                  Follow the answer

                   

                  CARGA_INCREMENTAL:

                  LOAD

                  ID_SYSTEM,

                  OID_METER,

                  DATE_READ,

                  ORA_ROWSCN,

                  DATA_E_HORA;

                   

                   

                  select i.id_system,i.ora_rowscn,m.oid_meter,date_read,scn_to_timestamp(i.ora_rowscn) data_e_hora

                                        from rs_individual_read i, rs_uc u, rs_meter m, rs_counters c

                                        where m.oid_meter = c.oid_meter

                                          and c.concentrator_counters = i.concentrator

                                          and c.port_counters = i.port

                                           and date_read between c.dt_init and c.dt_end

                                          and date_read between m.dt_init and m.dt_end

                                          and u.oid_uc=m.oid_uc

                                          and u.oid_system=i.id_system

                                          and i.id_system =20

                                           and i.ora_rowscn > TIMESTAMP_TO_SCN(SYSDATE-5)