Problem with record selection

[MellowAl]

Crystal Reports 8.5, ODBC connection.
This is a help desk system.

IsNull ({tasks.COMPLETED})
returns four task records:
23,206
23,380
23,322
24,511

IsNull ({tasks.COMPLETED}) and {tasks.PRIORITY} = "Hold" returns one task record:
23,206

IsNull ({tasks.COMPLETED}) and {tasks.PRIORITY} <> &quot;Hold&quot;
returns NO task records!!

Why not? I was hoping it would return the other three records.

Alice Hengst

 

[synapsevampire]

Try:

IsNull ({tasks.COMPLETED}) and (isnull({tasks.PRIORITY}) or {tasks.PRIORITY}<> &quot;Hold&quot

They're null.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[MellowAl]

Thanks a lot, that did the trick!

Does this mean that I will always have to test for null when I want to exclude records with a particular field value? Is this a normal database design, or did the designer just make it particularly difficult?

Alice

 

[Naith]

It's normal for most databases to have constraints which prevent some fields on each table from being null. These fields are force a value, as they are usually part of an index or the primary key of the table, which makes sure that each row is unique.

As you can usually ensure the uniqueness of each row in the first few columns - or even the first column - the additional columns usually don't have constraints, and so will accept null values.

The fact that your database contains null values isn't necessarily bad database design (although some environments will force a default value to replace nulls), it may be that your data simply needs to be handled this way.

If you're unsure of what the primary keys are for tables, then Crystal's Visual Linking Expert goes some way to indicating indexed fields. So, in response to your question, if you're going to be querying a field which isn't part of the table's primary key, or included in any of the table's indices, then it's best practice to consider checking for nulls in your formulas. As your example shows you, skipping null checks doesn't usually give you an error, so it can be easy to accidentally miss data you would have otherwise liked to have reported on.

Good luck with your report,

Naith