Exclude Records

[luiluigi]

Hi Community.

I have two simple resident tables. Lets say Temp1 and Temp2...

Temp1:

Load * Inline [

A|B

1|abc

2|abc

3|abc

3|abc

3|abc

] (delimiter is '|');

Temp2:

Load * Inline [

A|C

1|xyz

2|xyz

4|xyz

] (delimiter is '|');

In final result table Tab2 I want to get only those records from resident table Temp2 that don't exist in the other resident table Temp1. The result should be obviously record 4|xyz...I use following code...

Tab1:

NoConcatenate Load * Resident Temp1;

Drop Table Temp1;

Tab2:

NoConcatenate Load * Resident Temp2 Where not Exists(A);

Drop Tables Temp2, Tab1;

The result is table Tab2 with no records...

Please, any hints where the problem is?

TL

[sunny_talwar]

Try this when you load the Temp2 table because once you do the resident load.... 4 is now available from Temp2

Temp1:

Load * Inline [

A|B

1|abc

2|abc

3|abc

3|abc

3|abc

] (delimiter is '|');

Temp2:

Load * Inline [

A|C

1|xyz

2|xyz

4|xyz

] (delimiter is '|')

Where not Exists(A);

[luiluigi · Accepted solution]

Hi Sunny,

Thank you for your idea. It's clear and simple, but it is not the right solution for my issue. I cannot use the WHERE clause while loading data into Temp2 table. In real app/model the situation is not that easy. I concatenate/join the information in Temp1 and Temp2 from several other tables using different conditions.

I have to read data/compare data from/between these two resident Temp tables!

Nevertheless, after deeper study of help.click.com I found the right solution...

Here it is...

Temp1:

Load * Inline [

A|B

1|abc

2|abc

3|abc

3|abc

3|abc

] (delimiter is '|');

Temp2:

Load * Inline [

C|D

1|xyz

2|xyz

4|xyz

] (delimiter is '|');

Result:

Load

C as A,

D

Resident Temp2 Where not Exists(A,C);

Drop Tables Temp1, Temp2;