Detecting orphaned records

[#1]

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 !

[boorgura]

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.

[#3]

Hello Adriano , with the script right join the result is 59, is the value orphaned

Load

PART_CODE

Resident

PRODUCT

where

PART_CODE1 <> PART_CODE2;

Drop

table

PRODUCT;

[#4]

But we need to check (dump) PRODUCT B as well cause it points to a non existing PART (99)....

[pablolabbe]

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>

I´ve used NOCONCATENATE because PRODUCT table is already in memory. If you read directly from QVD it´s not necessary.