Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello !
Many times we need to check two tables, associated by a key, to check for orphaned records. That situation SHOULD NOT OCCUR on the original database but, sometimes it happens.
An example ?
Say that you have a PRODUCTS COMPOSITION table, where you list all of the parts that will build up the product. Then you have another PARTS table, item per item.
So, an error could be a PRODUCT using an non existing part or a part that is not used in any product.
I'd like to list those problems or build a table only with those records. At the end of the script I will STORE that table into a TXT and send it via email to someone else reporting the problems.
As usual I have provided a very practical sample application.
Thanks in advance !
Adriano,
Nice question.
This is some what similar to the Set analysis in Math.
Where we have 2 sets A & B, so you need the Union of (A - B) and (B - A)
Will try to see if I can code the logic for the same.
Hello Adriano , with the script right join the result is 59, is the value orphaned
PART_CODE
Resident
PRODUCT
where
PART_CODE1 <> PART_CODE2;Drop
tablePRODUCT;
But we need to check (dump) PRODUCT B as well cause it points to a non existing PART (99)....
this code creates a new table only with products without parts
PRODUCT_WO_PARTS:
NOCONCATENATE LOAD * RESIDENT PRODUCT;
JOIN LOAD PART_CODE,PART_NAME RESIDENT PARTS;
RIGHT JOIN LOAD PRODUCT_NAME, PART_CODE RESIDENT PRODUCT_WO_PARTS
WHERE ISNULL(PART_NAME);<div>