Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi people,
I need to make a incremental charge, for that reason i load my qvd and load my new query, after i need to join only the results that doesn't exist on the qvd.
How can i exclude this values, cause Exist() could not use in diferent tables?
****load the qvd
CARGA_INCREMENTAL:
LOAD
*
FROM [lib://MI_csv/Leituras/Individual_Read/RS_INDIVIDUAL_READ_$(vCarga).qvd](qvd);
***** get the latest update
DATA_ATUALIZACAO:
LOAD
timestamp(MAX(DATA_SERV),'DD/MM/YYYY hh:mm:ss') AS DATAMAX
RESIDENT CARGA_INCREMENTAL;
Let vDatamax=FieldValue('DATA_SERV',1);
Concatenate(CARGA_INCREMENTAL)
CARGA_INCREMENTAL2:
LOAD
ID_SYSTEM,
DATE_READ,
ORA_ROWSCN,
PORT,
VALUE_READ,
DATA_SERV,
CONCENTRATOR
WHERE DATA_SERV>'$(vDatamax)'
;
SELECT
id_system,
port,
date_read,
value_read,
concentrator,
ora_rowscn,
scn_to_timestamp(ora_rowscn) data_serv
from rs_individual_read
where date_read between TO_DATE(SYSDATE) and TO_DATE(SYSDATE+1);
Hi everyone, i finally did. Follow the final code if someone need for something
CARGA_INCREMENTAL:
LOAD
*
FROM [lib://MI_csv/Leituras/Individual_Read/RS_INDIVIDUAL_READ_$(vCarga).qvd](qvd);
Concatenate(CARGA_INCREMENTAL)
LOAD
ID_SYSTEM,
DATE_READ,
PORT,
VALUE_READ,
CONCENTRATOR,
ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA;
SELECT
id_system,
port,
date_read,
value_read,
concentrator,
ora_rowscn,
scn_to_timestamp(ora_rowscn) data_serv
from rs_individual_read
//where ora_rowscn>timestamp_to_scn(SYSDATE-1) AND (date_read between TO_DATE(SYSDATE-2) and TO_DATE(SYSDATE+1));
where ora_rowscn>timestamp_to_scn(sysdate - interval '1' hour)
AND(date_read between TO_DATE(SYSDATE-2) and TO_DATE(SYSDATE+1));
//this solved my problem
NoConcatenate
QVD_ATUALIZADO:
Load
Distinct
ID_SYSTEM,
DATE_READ,
PORT,
VALUE_READ,
CONCENTRATOR,
ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA
RESIDENT CARGA_INCREMENTAL;
Drop Table CARGA_INCREMENTAL;
I think that this line:
Let vDatamax=FieldValue('DATA_SERV',1);
Should be:
Let vDatamax=FieldValue('DATA_MAX',1);
Hi ,
Try this for setting the max date,
Let vDatamax= Num(Peek('DATAMAX',0,'DATA_ATUALIZACAO'));
Thanks,
Hi guys,
Petter is right about the field name, but im still with the same result 2 tables that i dont know how to concate.
But really thank's for help me
You've got a little bit of redundant code. Delete 'CARGA_INCREMENTAL2, as I presume this is overriding the prior Concatenate statement:
Concatenate(CARGA_INCREMENTAL)
CARGA_INCREMENTAL2:
LOAD
ID_SYSTEM,
Hi everyone, i finally did. Follow the final code if someone need for something
CARGA_INCREMENTAL:
LOAD
*
FROM [lib://MI_csv/Leituras/Individual_Read/RS_INDIVIDUAL_READ_$(vCarga).qvd](qvd);
Concatenate(CARGA_INCREMENTAL)
LOAD
ID_SYSTEM,
DATE_READ,
PORT,
VALUE_READ,
CONCENTRATOR,
ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA;
SELECT
id_system,
port,
date_read,
value_read,
concentrator,
ora_rowscn,
scn_to_timestamp(ora_rowscn) data_serv
from rs_individual_read
//where ora_rowscn>timestamp_to_scn(SYSDATE-1) AND (date_read between TO_DATE(SYSDATE-2) and TO_DATE(SYSDATE+1));
where ora_rowscn>timestamp_to_scn(sysdate - interval '1' hour)
AND(date_read between TO_DATE(SYSDATE-2) and TO_DATE(SYSDATE+1));
//this solved my problem
NoConcatenate
QVD_ATUALIZADO:
Load
Distinct
ID_SYSTEM,
DATE_READ,
PORT,
VALUE_READ,
CONCENTRATOR,
ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA
RESIDENT CARGA_INCREMENTAL;
Drop Table CARGA_INCREMENTAL;