How to do NOT IN Statement when doing a load from a Resident table

[#1]

Hello,

I'm stuck at not being able to do not in statement during the load statement of one of the resident table. Here's my scenario:

Table 1:

ID, T1_Field1, T_1Field2

1, f1A, F2A

2, f1B, F2B

3, f1C, F2C

Table 2:

ID, T2_Field1, T_2Field2

2, f1B, F2B

I need to get all the records from Table 1 that is not in Table 2

So my result of my query should result:

Table 1:

ID, T1_Field1, T_1Field2

1, f1A, F2A

3, f1C, F2C

Note: Both of these tables are resident tables. Thanks in advance.

-Samir

[fosuzuki · Accepted solution]

Hi Samir,

check this sample code:

Table1:
load * inline [
ID, T1_Field1, T_1Field2
 1, f1A, F2A
 2, f1B, F2B
 3, f1C, F2C
];

Table2:
noconcatenate
load * inline [
 ID2, T2_Field1, T_2Field2
 2, f1B, F2B
];

DesiredTable:
noconcatenate
load * resident Table1
where not exists (ID2, ID);

drop table Table1, Table2;

Hope this helps you.

Regards,

Fernando

[#3]

Thanks! That did the trick.