Concatenate WHERE NOT EXISTS(Composite Key) is not working as expected

[pjyothsna1311]

Hi,

I have two scripts to load data. One is Initial Load and other is Incremental Load.

During Initial Load I will load all the data from DB and store in to one QVD file (Test.qvd)

And Incremental Load script runs for every one minute and will fetch the data that has been added in that one min time interval and the result will be concatenated with main QVD file (Test.qvd)

So I wrote a script as below

[student_data]

select <some records> from student a inner join school b on a.stud_id = b.stud.id and a.stud_name = b.stud_name where b.date_time > 'lastexectime';

concatenate(student_data)

Load *from Test.qvd where not exists(stud_id,stud_name);

In this script I have stud_id,stud_name as composite key. Because of this composite key where not exists is not working properly and I am getting duplicate records when I update any record in DB.

Can someone please suggest me the proper solution for this.

[sunny_talwar]

Can you just not do it based on the Stud ID? Isn't that a unique identifier in your data?

[Vu_Nguyen]

Hi, Exists function accepts only one field on the source table and not composite keys. 

If you want to check existence on more than 1 field, you will need to make a key field from a concatenation of these fields. For example in your case:

[student_data]:
LOAD 
    stud_id & '-' & stud_name as stud_key,
    *;
select 
   <some records> 
from student a inner join school b on a.stud_id = b.stud.id and a.stud_name = b.stud_name where b.date_time > 'lastexectime';

concatenate(student_data)

Load * from Test.qvd where not exists(stud_key);

 Of course the qvd file should contain the same stud_key field.

Hope this helps!