Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Introducing a new Enhanced File Management feature in Qlik Cloud! GET THE DETAILS!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
fred31
Contributor
Contributor
‎2023-03-10 11:44 AM

Incremental LOAD from Oracle DB

Hello,

I have an issue concerning Incremental Loading inside my qvd from Oracle DB

I would like to add data into my qvd only if the date is changing. I would be able to load my data when I desire (every hour, if I need for testing, without any change (doublons) and everyday with change because new date).

I would like to use DATE_EXTRACT (coming from my sql queries). 

I assume there are mistakes in my code and I need help for that too.

Here a view of my app (with a part of "Initial loading"):

fred31_0-1678465372155.png

Details:

1- SECTION Initial Loading:

///////////////////////////////
// [EQPT] INITIAL LOADING
///////////////////////////////
LOAD *;
Eqpt:

SQL select TRUNC(sysdate) AS DATE_EXTRACT, r.DFS_NAME, d.FILE_NAME, d.DATA_TYPE, d.PART_NAME, d.LAST_MODIFICATION_DATE, 'EQPT' as TYPE from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) like '%EQTCV5U%' and d.DATA_TYPE = '_ps' and UPPER(d.PART_NAME) in (
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) like '%EQTCV5U%' and d.DATA_TYPE = '_ps'
minus
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) like '%EQTCV5U%' and UPPER(d.DATA_TYPE) = 'CATPRODUCT');


Concatenate(Eqpt)
LOAD *;
SQL select TRUNC(sysdate) AS DATE_EXTRACT, r.DFS_NAME, d.FILE_NAME, d.DATA_TYPE, d.PART_NAME, d.LAST_MODIFICATION_DATE, 'EQPT' as TYPE from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) like '%EQTCV5U%' and UPPER(d.DATA_TYPE) = 'CATPRODUCT' and UPPER(d.PART_NAME) in (
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) like '%EQTCV5U%' and UPPER(d.DATA_TYPE) = 'CATPRODUCT'
minus
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) like '%EQTCV5U%' and d.DATA_TYPE = '_ps');


// stockage officiel
Store Eqpt into 'lib://path_qvd/Official2.qvd' (qvd);
drop table Eqpt;

///////////////////////////////
// [CONCEPTION] INITIAL LOADING
///////////////////////////////

Conception:
LOAD *
FROM [lib://path_qvd/Official2.qvd] (qvd);
Concatenate (Conception)

LOAD *;
SQL
select TRUNC(sysdate) AS DATE_EXTRACT, r.DFS_NAME, d.FILE_NAME, d.DATA_TYPE, d.PART_NAME, d.LAST_MODIFICATION_DATE, 'CONCEPTION' as TYPE from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBUL_R16_V5', 'SB04_R16_V5', 'SB03_R16_V5', 'SBTAI_R16_V5', 'SBMAG_R16_V5', 'SBBAB_R16_V5', 'SB03N_R16_V5', 'SBECA_R16_V5', 'SACOC_R16_V5', 'SBAED_R16_V5', 'SAIEVN_R16_V5', 'SAFOE_R16_V5', 'SB10_R16_V5', 'SB02_R16_V5', 'SB01_R16_V5', 'SBFECA_R16_V5', 'SBDAH_R16_V5', 'SBAE_R16_V5', 'SBEAED_R16_V5', 'SBEAE_R16_V5', 'SBP2F_R16_V5', 'SBIEV_R16_V5', 'SBRAD_R16_V5', 'SANEOD_R16_V5', 'SASOGD_R16_V5', 'SAALND_R16_V5', 'SADAHR_R16_V5')
and UPPER(d.DATA_TYPE) = 'CATPRODUCT' and UPPER(d.PART_NAME) in (
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBUL_R16_V5', 'SB04_R16_V5', 'SB03_R16_V5', 'SBTAI_R16_V5', 'SBMAG_R16_V5', 'SBBAB_R16_V5', 'SB03N_R16_V5', 'SBECA_R16_V5', 'SACOC_R16_V5', 'SBAED_R16_V5', 'SAIEVN_R16_V5', 'SAFOE_R16_V5', 'SB10_R16_V5', 'SB02_R16_V5', 'SB01_R16_V5', 'SBFECA_R16_V5', 'SBDAH_R16_V5', 'SBAE_R16_V5', 'SBEAED_R16_V5', 'SBEAE_R16_V5', 'SBP2F_R16_V5', 'SBIEV_R16_V5', 'SBRAD_R16_V5', 'SANEOD_R16_V5', 'SASOGD_R16_V5', 'SAALND_R16_V5', 'SADAHR_R16_V5') and UPPER(d.DATA_TYPE) = 'CATPRODUCT'
minus
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBUL_R16_PS', 'SB04_R16_PS', 'SB03_R16_PS', 'SBTAI_R16_PS', 'SBMAG_R16_PS', 'SBBAB_R16_PS', 'SB03N_R16_PS', 'SBECA_R16_PS', 'SACOC_R16_PS', 'SBAED_R16_PS', 'SAIEVN_R16_PS', 'SAFOE_R16_PS', 'SB10_R16_PS', 'SB02_R16_PS', 'SB01_R16_PS', 'SBFECA_R16_PS', 'SBDAH_R16_PS', 'SBAE_R16_PS', 'SBEAED_R16_PS', 'SBEAE_R16_PS', 'SBP2F_R16_PS', 'SBIEV_R16_PS', 'SBRAD_R16_PS', 'SANEOD_R16_PS', 'SASOGD_R16_PS', 'SAALND_R16_PS', 'SADAHR_R16_PS') and d.DATA_TYPE = '_ps');

// stockage officiel
Store Conception into 'lib://path_qvd/Official2.qvd' (qvd);

drop table Conception;

// ///////////////////////////////
// // [BIBLIOTHEQUE] INITIAL LOADING
// ///////////////////////////////

Bibliotheque:
LOAD *
FROM [lib://path_qvd/Official2.qvd] (qvd);
Concatenate (Bibliotheque)

LOAD *;

SQL
select TRUNC(sysdate) AS DATE_EXTRACT, r.DFS_NAME, d.FILE_NAME, d.DATA_TYPE, d.PART_NAME, d.LAST_MODIFICATION_DATE, 'BIBLIOTHEQUE' as TYPE from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBBEQ_R16_V5')
and UPPER(d.DATA_TYPE) = 'CATPRODUCT' and UPPER(d.PART_NAME) in (
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBBEQ_R16_V5') and UPPER(d.DATA_TYPE) = 'CATPRODUCT'
minus
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBBEQ_R16_PS') and d.DATA_TYPE = '_ps');

 

// stockage officiel
Store Bibliotheque into 'lib://path_qvd/Official2.qvd' (qvd);

drop table Bibliotheque;

///////////////////////////////
// [BASELINE] INITIAL LOADING
///////////////////////////////

Baseline:
LOAD *
FROM [lib://path_qvd/Official2.qvd] (qvd);
Concatenate (Baseline)

LOAD *;

SQL

select TRUNC(sysdate) AS DATE_EXTRACT, r.DFS_NAME, d.FILE_NAME, d.DATA_TYPE, d.PART_NAME, d.LAST_MODIFICATION_DATE, 'BASELINE' as TYPE from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SB_R16_V5')
and UPPER(d.DATA_TYPE) = 'CATPRODUCT' and UPPER(d.PART_NAME) in (
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SB_R16_V5') and UPPER(d.DATA_TYPE) = 'CATPRODUCT'
minus
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SB_R16_PS') and d.DATA_TYPE = '_ps');


// stockage officiel
Store Baseline into 'lib://path_qvd/Official2.qvd' (qvd);
drop table Baseline;

///////////////////////////////
// [TRAVAUX INTERNES] INITIAL LOADING
///////////////////////////////

Travaux_Internes:
LOAD *
FROM [lib://path_qvd/Official2.qvd] (qvd);
Concatenate (Travaux_Internes)

LOAD *;

SQL
select TRUNC(sysdate) AS DATE_EXTRACT, r.DFS_NAME, d.FILE_NAME, d.DATA_TYPE, d.PART_NAME, d.LAST_MODIFICATION_DATE, 'TRAVAUX_INTERNES' as TYPE from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SADMS_R16_V5', 'SACD_R16_V5', 'SB60_R16_V5', 'SB50_R16_V5', 'SB40_R16_V5', 'SADOOR_R16_V5', 'SBEXM_R16_V5', 'SB19_R16_V5', 'SB19J_R16_V5', 'SAAECI_R16_V5', 'SB20_R16_V5', 'SANEO_R16_V5', 'SASOG_R16_V5', 'SASHAR_R16_V5', 'SAS14A_R16_V5', 'SBIPM_R16_V5', 'SBNIS3DX_R16_V5', 'SAAEQ_R16_V5')
and UPPER(d.DATA_TYPE) = 'CATPRODUCT' and UPPER(d.PART_NAME) in (
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SADMS_R16_V5', 'SACD_R16_V5', 'SB60_R16_V5', 'SB50_R16_V5', 'SB40_R16_V5', 'SADOOR_R16_V5', 'SBEXM_R16_V5', 'SB19_R16_V5', 'SB19J_R16_V5', 'SAAECI_R16_V5', 'SB20_R16_V5', 'SANEO_R16_V5', 'SASOG_R16_V5', 'SASHAR_R16_V5', 'SAS14A_R16_V5', 'SBIPM_R16_V5', 'SBNIS3DX_R16_V5', 'SAAEQ_R16_V5') and UPPER(d.DATA_TYPE) = 'CATPRODUCT'
minus
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SADMS_R16_PS', 'SACD_R16_PS', 'SB60_R16_PS', 'SB50_R16_PS', 'SB40_R16_PS', 'SADOOR_R16_PS', 'SBEXM_R16_PS', 'SB19_R16_PS', 'SB19J_R16_PS', 'SAAECI_R16_PS',
'SB20_R16_PS', 'SANEO_R16_PS', 'SASOG_R16_PS', 'SASHAR_R16_PS', 'SAS14A_R16_PS', 'SBIPM_R16_PS', 'SBNIS3DX_R16_PS', 'SAAEQ_R16_PS') and d.DATA_TYPE = '_ps');

 

// stockage officiel
Store Travaux_Internes into 'lib://path_qvd/Official2.qvd' (qvd);
drop table Travaux_Internes;

///////////////////////////////
// [DEFINITION] INITIAL LOADING
///////////////////////////////

Definition:
LOAD *
FROM [lib://path_qvd/Official2.qvd] (qvd);
Concatenate (Definition)

LOAD *;

SQL
select TRUNC(sysdate) AS DATE_EXTRACT, r.DFS_NAME, d.FILE_NAME, d.DATA_TYPE, d.PART_NAME, d.LAST_MODIFICATION_DATE, 'DEFINITION' as TYPE from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBEC_R16_V5', 'SB30_R16_V5')
and UPPER(d.DATA_TYPE) = 'CATPRODUCT' and UPPER(d.PART_NAME) in (
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBEC_R16_V5', 'SB30_R16_V5') and UPPER(d.DATA_TYPE) = 'CATPRODUCT'
minus
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBEC_R16_PS', 'SB30_R16_PS') and d.DATA_TYPE = '_ps');


// stockage officiel
Store Definition into 'lib://path_qvd/Official2.qvd' (qvd);
drop table Definition;

///////////////////////////////
// [PARTENAIRE] INITIAL LOADING
///////////////////////////////

Partenaire:
LOAD *
FROM [lib://path_qvd/Official2.qvd] (qvd);
Concatenate (Partenaire)
LOAD *;

SQL
select TRUNC(sysdate) AS DATE_EXTRACT, r.DFS_NAME, d.FILE_NAME, d.DATA_TYPE, d.PART_NAME, d.LAST_MODIFICATION_DATE, 'PARTENAIRE' as TYPE from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBXUL_R16_V5', 'SB03R_R16_V5', 'SBBF_R16_V5', 'SBXAD%_R16_V5', 'SBBAE_R16_V5')
and UPPER(d.DATA_TYPE) = 'CATPRODUCT' and UPPER(d.PART_NAME) in (
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBXUL_R16_V5', 'SB03R_R16_V5', 'SBBF_R16_V5', 'SBXAD%_R16_V5', 'SBBAE_R16_V5') and UPPER(d.DATA_TYPE) = 'CATPRODUCT'
minus
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBXUL_R16_PS', 'SB03R_R16_PS', 'SBBF_R16_PS', 'SBXAD%_R16_PS', 'SBBAE_R16_PS') and d.DATA_TYPE = '_ps');

 

// stockage officiel
Store Partenaire into 'lib://path_qvd/Official2.qvd' (qvd);
drop table Partenaire;

///////////////////////////////
// [MAQUETTE_REFERENCE_GEOMETRIQUE] INITIAL LOADING
///////////////////////////////

MRG:
LOAD *
FROM [lib://path_qvd/Official2.qvd] (qvd);
Concatenate (MRG)

LOAD *;

SQL
select TRUNC(sysdate) AS DATE_EXTRACT, r.DFS_NAME, d.FILE_NAME, d.DATA_TYPE, d.PART_NAME, d.LAST_MODIFICATION_DATE, 'MRG' as TYPE from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) like 'SBBDA%_R16_V5'
and UPPER(d.DATA_TYPE) = 'CATPRODUCT' and UPPER(d.PART_NAME) in (
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) like 'SBBDA%_R16_V5' and UPPER(d.DATA_TYPE) = 'CATPRODUCT'
minus
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) like 'SBBDA%_R16_PS' and d.DATA_TYPE = '_ps');

 

// stockage officiel
Store MRG into 'lib://path_qvd/Official2.qvd' (qvd);
drop table MRG;

/////////////////////////////
// [SBTEMP] INITIAL LOADING
/////////////////////////////

SBTEMP:
LOAD *
FROM [lib://path_qvd/Official2.qvd] (qvd);
Concatenate (SBTEMP)

LOAD *;

SQL
select TRUNC(sysdate) AS DATE_EXTRACT, r.DFS_NAME, d.FILE_NAME, d.DATA_TYPE, d.PART_NAME, d.LAST_MODIFICATION_DATE, 'SBTEMP' as TYPE from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBTEMP_R16_V5')
and UPPER(d.DATA_TYPE) = 'CATPRODUCT' and UPPER(d.PART_NAME) in (
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBTEMP_R16_V5') and UPPER(d.DATA_TYPE) = 'CATPRODUCT'
minus
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBTEMP_R16_PS') and d.DATA_TYPE = '_ps');


// stockage officiel
Store SBTEMP into 'lib://path_qvd/Official2.qvd' (qvd);
drop table SBTEMP;

///////////////////////////////
// [SBRUSED] INITIAL LOADING
///////////////////////////////

SBRUSED:
LOAD *
FROM [lib://path_qvd/Official2.qvd] (qvd);
Concatenate (SBRUSED)

LOAD *;

SQL
select TRUNC(sysdate) AS DATE_EXTRACT, r.DFS_NAME, d.FILE_NAME, d.DATA_TYPE, d.PART_NAME, d.LAST_MODIFICATION_DATE, 'SBRUSED' as TYPE from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBRUSED_R16_V5')
and UPPER(d.DATA_TYPE) = 'CATPRODUCT' and UPPER(d.PART_NAME) in (
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBRUSED_R16_V5') and UPPER(d.DATA_TYPE) = 'CATPRODUCT'
minus
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBRUSED_R16_PS') and d.DATA_TYPE = '_ps');


// stockage officiel
Store SBRUSED into 'lib://path_qvd/Official2.qvd' (qvd);
drop table SBRUSED;

///////////////////////////////
// [CADLIB EQPT & FTI] INITIAL LOADING
///////////////////////////////

SPI:
LOAD *
FROM [lib://path_qvd/Official2.qvd] (qvd);
Concatenate (SPI)

LOAD *;

SQL
select TRUNC(sysdate) AS DATE_EXTRACT, r.DFS_NAME, d.FILE_NAME, d.DATA_TYPE, d.PART_NAME, d.LAST_MODIFICATION_DATE, 'SPI' as TYPE from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) like '%CV5U%'
and UPPER(d.DATA_TYPE) = 'CATPRODUCT' and UPPER(d.PART_NAME) in (
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) like '%CV5U%' and UPPER(d.DATA_TYPE) = 'CATPRODUCT'
minus
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) like '%CV5U%' and d.DATA_TYPE = '_ps');


// stockage officiel
Store SPI into 'lib://path_qvd/Official2.qvd' (qvd);
drop table SPI;


///////////////////////////////
// [CADLIB SPL] INITIAL LOADING
///////////////////////////////

SPL:
LOAD *
FROM [lib://path_qvd/Official2.qvd] (qvd);
Concatenate (SPL)

LOAD *;

SQL
select TRUNC(sysdate) AS DATE_EXTRACT, r.DFS_NAME, d.FILE_NAME, d.DATA_TYPE, d.PART_NAME, d.LAST_MODIFICATION_DATE, 'SPL' as TYPE from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('CADLIB_ELEC','CADLIB_STP','CADLIB_TUBING')
and UPPER(d.DATA_TYPE) = 'CATPRODUCT' and UPPER(d.PART_NAME) in (
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('CADLIB_ELEC','CADLIB_STP','CADLIB_TUBING') and UPPER(d.DATA_TYPE) = 'CATPRODUCT'
minus
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('CADLIB_ELEC','CADLIB_STP','CADLIB_TUBING') and d.DATA_TYPE = '_ps');

 

// stockage officiel
Store SPL into 'lib://path_qvd/Official2.qvd' (qvd);
drop table SPL;


LOAD
DATE_EXTRACT,
DFS_NAME,
FILE_NAME,
DATA_TYPE,
PART_NAME,
LAST_MODIFICATION_DATE,
"TYPE"
FROM [lib://path_qvd/Official2.qvd]
(qvd);


For incremental, I have one SECTION for each part like: EQPT, CONCEPTION, BASELINE....

Example CONCEPTION Section for incremental:

CurrentQVD_Table:
LOAD
max(DATE_EXTRACT) as max_date
FROM [lib://path_qvd/Official2.qvd] (qvd);

//Store in a variable

// Let vMaxQvdDate = Date(Peek(max_date,0,WAT),'YYYYMMDD');
Let vMaxQvdDate = Peek('max_date' ,0,'CurrentQVD_Table');

// trace $(vMaxQvdDate);

LIB CONNECT TO 'ORACLE_DB';

Conception:
LOAD *;

SQL
select TRUNC(sysdate) AS DATE_EXTRACT, r.DFS_NAME, d.FILE_NAME, d.DATA_TYPE, d.PART_NAME, d.LAST_MODIFICATION_DATE, 'CONCEPTION' as TYPE from U_1W50.T_ROA r where TRUNC(sysdate) >'$(vMaxQvdDate)'
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBUL_R16_V5', 'SB04_R16_V5', 'SB03_R16_V5', 'SBTAI_R16_V5', 'SBMAG_R16_V5', 'SBBAB_R16_V5', 'SB03N_R16_V5', 'SBECA_R16_V5', 'SACOC_R16_V5', 'SBAED_R16_V5', 'SAIEVN_R16_V5', 'SAFOE_R16_V5', 'SB10_R16_V5', 'SB02_R16_V5', 'SB01_R16_V5', 'SBFECA_R16_V5', 'SBDAH_R16_V5', 'SBAE_R16_V5', 'SBEAED_R16_V5', 'SBEAE_R16_V5', 'SBP2F_R16_V5', 'SBIEV_R16_V5', 'SBRAD_R16_V5', 'SANEOD_R16_V5', 'SASOGD_R16_V5', 'SAALND_R16_V5', 'SADAHR_R16_V5')
and UPPER(d.DATA_TYPE) = 'CATPRODUCT' and UPPER(d.PART_NAME) in (
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBUL_R16_V5', 'SB04_R16_V5', 'SB03_R16_V5', 'SBTAI_R16_V5', 'SBMAG_R16_V5', 'SBBAB_R16_V5', 'SB03N_R16_V5', 'SBECA_R16_V5', 'SACOC_R16_V5', 'SBAED_R16_V5', 'SAIEVN_R16_V5', 'SAFOE_R16_V5', 'SB10_R16_V5', 'SB02_R16_V5', 'SB01_R16_V5', 'SBFECA_R16_V5', 'SBDAH_R16_V5', 'SBAE_R16_V5', 'SBEAED_R16_V5', 'SBEAE_R16_V5', 'SBP2F_R16_V5', 'SBIEV_R16_V5', 'SBRAD_R16_V5', 'SANEOD_R16_V5', 'SASOGD_R16_V5', 'SAALND_R16_V5', 'SADAHR_R16_V5') and UPPER(d.DATA_TYPE) = 'CATPRODUCT'
minus
select UPPER(d.PART_NAME) from U_1W50.T_ROA r
inner join U_1W50.T_ROA_DATA d
on d.ROA_ID = r.ROA_ID
where UPPER(r.ROA_NAME) IN('SBUL_R16_PS', 'SB04_R16_PS', 'SB03_R16_PS', 'SBTAI_R16_PS', 'SBMAG_R16_PS', 'SBBAB_R16_PS', 'SB03N_R16_PS', 'SBECA_R16_PS', 'SACOC_R16_PS', 'SBAED_R16_PS', 'SAIEVN_R16_PS', 'SAFOE_R16_PS', 'SB10_R16_PS', 'SB02_R16_PS', 'SB01_R16_PS', 'SBFECA_R16_PS', 'SBDAH_R16_PS', 'SBAE_R16_PS', 'SBEAED_R16_PS', 'SBEAE_R16_PS', 'SBP2F_R16_PS', 'SBIEV_R16_PS', 'SBRAD_R16_PS', 'SANEOD_R16_PS', 'SASOGD_R16_PS', 'SAALND_R16_PS', 'SADAHR_R16_PS') and d.DATA_TYPE = '_ps');

Store Conception into 'lib://path_qvd/Official2.qvd' (qvd);

drop table Conception;

LOAD
DATE_EXTRACT,
DFS_NAME,
FILE_NAME,
DATA_TYPE,
PART_NAME,
LAST_MODIFICATION_DATE,
"TYPE"
FROM [lib://path_qvd/Official2.qvd]
(qvd);

 

I hope, I'm clear.

If not please do not hesitate to ask me more details. I really need help.

Thanks a lot for your help

Regards

 

 

Labels (1)
Labels
225 Views
0 Likes
0 Replies