Missing values in join

[#1]

Hi,

We have got an application where invoices are joined with contracts based on a contractnumber. Some of the contractnumbers in our invoices appaer to be missing from the contracttable. This shouldn't be possible.. We want to retrieve this contractnumbers. I can see the missing contractnumbers from the contracttable because they are represented by a NULL ('-') symbol in the main table on the same row as the contractnr from the invoice but I cannot select this NULL values.

Is there an easy way to replace this NULL values with a surrogat value to make selection possible? This way we can export the missing numbers at once or give the missing contracts a status. I have tried NULLASVALUE but this does not seem to work. Maybe because it is not a NULL value in a table but a missing link between two tables..

We bumped into this problem by accident, it would be great if we can build in a generic solution to check this kind of abnormalities..

Hope you can help!

Regards, Sander

[#2]

In your load, you could set the Null values to something else.

Oracle syntax:

NVL(field_name, 'Missing') As field_name

T SQL

IsNull(field_name, 'Missing') As field_name

This will need to be handled on a field by field basis. I don't know if there is a good way to handle this across your whole application.

[rwunderlich · Accepted solution]

Here are two approaches, using "Customer" as the missing field.

Assigning a special value to the missing fields:

RIGHT JOIN LOAD DISTINCT
 *,
 if(len(Customer) = 0, '*Missing*', Customer) as CustomerTemp
RESIDENT data;
DROP FIELD Customer;
RENAME FIELD CustomerTemp TO Customer;

Creating a new flag field to identify missings:

RIGHT JOIN LOAD DISTINCT
 *,
 if(len(Customer) = 0, 'Y') as "Missing Customer"
RESIDENT data
;

-Rob

[#4]

Thanks NMiller and Rob for your replies!

Using a RIGHT JOIN is the only possibility i guess.. This way tagging the missing field in the script is possible.

Regards, Sander