WHERE EXISTS using concatenated primary key for incremental load

[V_Ortis]

Hi everyone,

I need some help with this. I'm trying to create an incremental load from a database table. The issue is that the primary key of this table is composed of two concatenated fields.

I want to use a WHERE EXISTS clause to filter only the new or recently updated records, based on this concatenated key. However, I'm not sure how to properly implement it so I don’t miss any relevant data.

Have any of you faced a similar situation? Could you help me understand how to apply WHERE EXISTS when the key is made up of two fields?

For example:

Let vCampoIncremental = 'DATEALT';
Let vPK = 'NNUMEMCON';
Let vDataCorteIncremental = Date(Today());

//carregando dados do banco
[$(vTabela)]:
SQL SELECT * FROM $(vEsquema).$(vTabela)
WHERE $(vTabela).$(vCampoIncremental) >= TO_DATE('$(vDataCorteIncremental)','DD/MM/YYYY');

Concatenate
LOAD * FROM
[lib://Tabelas Puras:DataFiles/$(vEsquema)/$(vTabela).QVD](qvd)
WHERE NOT EXISTS($(vPK));

Inner Join SELECT "$(vPK)" FROM "$(vEsquema)"."$(vTabela)";

Store [$(vTabela)] into [lib://Tabelas Puras:DataFiles/$(vConexao)/$(vTabela).QVD];
DROP Table [$(vTabela)];

[Digvijay_Singh]

Not sure but did you try making a composite key like Field1 & '|'& Field2 and try WHERE NOT EXISTS(Field1 & '|'& Field2)? I might be missing something but wanted to understand what options you have tried..

[Or · Accepted solution]

With Exists(), it is better to use a composite key on both ends of the comparison as suggested by @Digvijay_Singh since this will allow optimized QVD reading. If you're using NOT EXISTS, this is off the table anyway so you can also pull the composite key from the first source and then concatenate on the fly for the second source. This will look something like:

Load Field1, Field2, Field3, Field1 & '|' Field2 as myKey

From Source1;

Load Field1, Field2, Field3

From Source2

Where Not Exists (myKey,Field1 & '|' & 'Field2')

[V_Ortis]

Yes, i tried using only field1&field2, but some datas got duplicated this way, so before saving the QVD i had to generate a unique key by concatenating both fields.