Placement of IsNull condition

[bhfmc]

This code in the Record Selection Formula editor (embedded in other selection criteria) does NOT return rows with null values, as I would expect:
({SARAPPD.SARAPPD_APDC_CODE} = '01' OR
{SARAPPD.SARAPPD_APDC_CODE} = '05' OR
IsNull ({SARAPPD.SARAPPD_APDC_CODE}))
When I place the IsNull condition first, the null values are returned as expected:
(IsNull ({SARAPPD.SARAPPD_APDC_CODE}) OR{SARAPPD.SARAPPD_APDC_CODE} = '01' OR
{SARAPPD.SARAPPD_APDC_CODE} = '05')
I am tempted to deduce that there is a correct order for the IsNull condition. Is this so? Is there documentation for this and other rules of order?

 

[Naith]

You've solved your own issue. There is a "feature" with the IsNull condition, which you've done well to spot; if used in a formula, IsNull must be handled first, otherwise, it's ignored.

I don't believe this is documented in the software online help file, but it may be noted in Crystal's online Knowledge Base.

Naith

 

[bhfmc]

Naith,
Thank you for the quick reply. How do I access Crystal's online Knowledge Base?

 

[GMcNamara]

http://support.crystaldecisions.com/library/kbase/articles/c2000247.asp

 

[synapsevampire]

This is a very annoying aspect of Crystal, and is NOT limited to the record selection formula, it's also true of formulas.

Note that the Crystal SQL Query (Database->Show SQL Query) passed reflects a correct query, but Crystal overrides it, as is evidenced by the following Trace (Profiler) performed against a SQL Server database:

SELECT NukeMe."longfield", NukeMe."updater", NukeMe."imanull", NukeMe."imnotnull" FROM "YRS_WORK"."dbo"."NukeMe" NukeMe WHERE (NukeMe."updater" = '1' OR NukeMe."updater" IS NULL)

-k