Remove Records in script

[#1]

I have two table, one with detail records (TableDetail) and the other (Table Status) hold the records status (which can be more then one). The link between the two tables is an Order Number. If in TableStatus one of the status's for say Order Number 123 is 1 then I do not want to bring in the record from TableDetail at all in the script.

How do I do this in the script?

Thanks,
Stephen

[#2]

I didn't know a way to delete records, but you can do something like this:

tmpRecordsToDelete:

LOAD OrderNumber AS OrderNumberToDelete

RESIDENT TableStatus

WHERE status = 1;

tmpTableDetail:

NOCONCATENATE

LOAD *

RESIDENT TableDetail

WHERE NOT Exists(OrderNumberToDelete, OrderNumber);

DROP TABLE tmpRecordsToDelete;

DROP TABLE TableDetail;

RENAME TABLE tmpTableDetail TO TableDetail;

[#3]

You can use a where not exists(1) statement.

[#4]

@Anthony It seems that the way you proupose is simpler. Could you explain it?

[#5]

Actually this is the way that I would do it after reading it all again. What the script does is loads all the table from two tables intwo one. Then loads it all into the resident table where orderstatus is not 1.

Table1:
load
order number
other fields
from source.qvd
join
load
order number
order status
from source2.qvd

load
Order number
order status
resident Table1 where order status <> 1;
Drop table Table1;

[#6]

Haven't tried the examples, but remember an order can have more the one status. So in the examples above are you only removing status 1. Meaning say an order has status's of 1, 2 and 3. Because the order has a 1 I do not want to see the order at all. So are the examples above going to continue to show the order because it also has status's of 2 and 3 (which I do not what)?

Stephen

[#7]

Does the order have an entry for each status?

[#8]

For an order in TableDetail (only one record for each order) there can be many records in TableStatus. For example, Order 123 in TableDetail can have in TableStatus: Status 1 (Order On Hold) and Status 2 (Order Not Shipped). Because the order is on hold (status 1) I do not want to see the record at all.

Hope this helps.

Stephen

[#9]

Try Damián D'Onia example then. IF it doesn't work let me know and I can write one up.

[#10]

Anthony, tried your suggestion and it removes status 1 for an order, but any other status's and the order number still remains in the resulting table. I want the order to not show at all (because one of the orders status's is 1).