Finding Orphans

[#1]

I have two tables both have the same key field (Sales Order together with Line Number). Table 1 has Sales Order and Line number 123-1, 123-2, 123-3 and 123-4. But Table 2 has line 1 2 and 3 but not 4.

I need a script that will take the two tables and the result is only items in table 2 that are not in table 1 (ie I only want to see 123-4)

How can I do this?

Thanks,

Stephen

[#2]

if the values are the same you could use not exists(key-1, key2) when loading the second table .

[#3]

Of course. have used before. It must be Friday! Thanks,

[#4]

Ok, I missunderstood a little the question.

You still can use not exists but you'll have to load the second table again with not exists(key-2, key-1).

Hope to be right this time.

[#5]

Carlos, hope you are still around. I am having trouble the resident part. Can you create a small example?

Thanks,
Stephen

[qw_johan]

Hi,

Try this...

Load * Resident Table1 where not exists(Table2.KEY, Table1.KEY);

Including example application.

Good luck,

Johan

[#7]

Hi,

You don't have to use resident unless you have have previously loaded the second table and you want to get your data from that table in memory.

You simply have to make sure that the first field to be used in the Exists( ) function has already been loaded when you use it.

Since you want the values in table 2 that don't exist in table 1, you'll have to do the next:

TABLE 1:

LOAD *

FROM OR RESIDENT WHATEVER YOUR SOURCE IS

(This includes the field Sales Order Line Number of the first table, suppose it is called Key1).

TABLE 2:

LOAD *

FROM OR RESIDENT WHATEVER YOUR SOURCE IS

(This includes the field Sales Order Line Number of the second table, suppose it is called Key2).

WHERE NOT EXISTS(Key1,Key2 );

So, the second table will only load the records that have a Sales Order Line Number that don't exist in Table 1.

Hope this helps!