Imagine a simple LEFT OUTER JOIN scenario:

SELECT person.Name, addr.Line1, addr.IsDefault
FROM Person person
LEFT OUTER JOIN Address addr ON person.PersonID = addr.PersonID
WHERE person.ID > 273

At the command line, I would expect to always get the person name, and optionally get the Line 1 and IsDefault if such a related record existed, eg:

John Wong   1234 Charwood Lane true
June Jones    4321 Happy St.          false
Mae June

When I place this in Crystal Reports (embedded version, Visual Studio 2005) I set the join settings in the Links tab to LEFT OUTER JOIN with Enforced Join set to 'Not Enforced'. So far, so good.

However, I also need to add one filter/formula:

{Address.IsDefault}

and this causes the problem:

My goal is for the report to show records 1 and 3 above. In other words, I only want Crystal Reports to perform the filter/formula when the Address record exists. Instead, it sees the lack of address as not meeting the filter/formula criteria and blocking row 3.

How do I get Crystal to allow row 3 while still maintaining the filter/formula?

Thanks,

David


Edited by dgadd - 08 May 2008 at 12:36pm

This is not a Crystal "feature" it's the way that SQL works, you ALWAYS have to test for Is Null BEFORE you test for a value.  This is because null is not really a value, it's the absence of a value.  If you compare a null with some value, the result is null instead of false like you would expect.