Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
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?
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];
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)
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?
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)