remove rows of table based on a second table

[amien]

i have two tables:

1\ orderID, invoiceID, itemID

2\ orderID, invoiceID

now ... i want to keep all rows in table 1 which are NOT in table 2

in other words: the rows in table 2 should be removed from table 1 (for all itemID's)

i only want to keep table 1, table 2 will be dropped.

[#2]

Please try "Where not Exist".

Load table 2 first and then use Where Not Exist to load Table1.

Easy is way is to make a Key with OrderID & InvoiceID.

[varunpbhandary]

Hi Amien,

               Another way of doing this is

tab1:

Load

orderid, invoiceid, itemid

from table1.qvw;

left join

Load

orderid, invoiceid, 1 as flag

from table2.qvw;

final:

Load

orderid, invoiceid, itemid, 'x' as junk

resident tab1 where flag<>1;

drop tab1;

[#4]

Hi,

You can use exists() function here.

Table2:

load

     orderID,

     invoiceID,

     orderID&'|'&invoiceID as check_table2

from abc.qvd;

Table1:

load

     orderID,

     invoiceID,

     itemID

from abc.qvd where not exists( check_table2, orderID&'|'&invoiceID );

drop field check_table2;

..

Ashutosh