Skip to Content
author's profile photo Former Member
Former Member

Why must you check for null first?

Post Author: sliese

CA Forum: Data Connectivity and SQL

Hi All -I am a bit confused. If I have a record selection formula like this:isnull({Product.Size}) or ({Product.Size} <> "xlrg")I realize that the null condition must come first in order for nulls to be included in the recordset. But, I don't understand why. Even if it can just be explained away by saying "that's a bug," isn't this being done by the dbms anyways? I thought the dbms looks at your SQL query strings, retrieves the appropriate records, then sends them back to Crystal. The dbms should give us the null values whether or not it comes first in the or condition. Am I missing something? Does crystal do some additional processesing after the records are retrieved that causes it to throw away all the null value records?Thanks in advance to anyone who can shed light on this issue for me.-Stephanie

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

8 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 30, 2008 at 06:28 PM

    Post Author: www.CrystalReportsBook.com

    CA Forum: Data Connectivity and SQL

    In a conditional statement, null values always return false. Doesn't matter what the condition is. So theoretically, a null "is not equal" to "xlrg" as you said in your post, but that doesn't matter. The condition returns false. This applies to both SQL Server and and Crystal Reports. That is why they created the special IsNull() function so that you can test for them.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 30, 2008 at 06:41 PM

    Post Author: kcheeb

    CA Forum: Data Connectivity and SQL

    The way I think of it is a Null is never equal to anything.

    If you use a where clause on a column that can contain Null values (Product.Size <> 'xlrg') and you want the Null values returned, you must use the IsNull function as part of the where clause.

    Hope this helps.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 30, 2008 at 09:48 PM

    Post Author: sliese

    CA Forum: Data Connectivity and SQL

    Right, I realize null values always return false. Which is exactly why I don't understand why it has to come first in record selection criteria. In other words, this works just fine:

    isnull(field) or field <> "xlrg"

    But, this does not, even though they are equivalent:

    field <> "xlrg" or isnull(field)

    When the DBMS first sees that I've checked if field <> xlrg, this will be false, thus it SHOULD go to the second criteria in my OR condition to determine whether or not to include the record. But, it does not seem to do that. Instead, it decides not to include the record at all even though the second part of the OR condition may still return true. Does my wording make sense now?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 30, 2008 at 10:34 PM

    Post Author: kcheeb

    CA Forum: Data Connectivity and SQL

    That is different behaviour.

    Which DBMS are you using and does it behave the same when you use a SQL tool to run the select statement.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 31, 2008 at 12:31 AM

    Post Author: sliese

    CA Forum: Data Connectivity and SQL

    I have seen this behavior on reports using both Oracle and Access databases.When running the query with a regular query tool, it does not matter what order it is in. This seems weird to me because I would assume the SQL part would only be affected by what the DBMS tried to do, but it seems as if Crystal is disregarding the null values after the dbms gives them to it.Any insight?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 31, 2008 at 03:50 PM

    Post Author: kcheeb

    CA Forum: Data Connectivity and SQL

    Have you tried this, And (IsNull(field) Or field <> 'xlrg') Adding brackets around the or'd statements.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 02, 2008 at 06:30 PM

    Post Author: KenHamady

    CA Forum: Data Connectivity and SQL

    The reason is the the Crystal Reports formula engine will throw an exception whenever it tries to evaluate a NULL (outside of the IsNull) and the formula simply stops on that record, returning a null value. When you put the IsNull first it never has to evaluate the Null because the first condition catches it and so it skips the second part. Ken Hamady

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 03, 2008 at 08:02 PM

    Post Author: GraemeG

    CA Forum: Data Connectivity and SQL

    I don't know if this helps in any way towards this thread - if it doesn't feel free to throw abude :o)

    As a rule, whenever I use a join in SQL that could give me a null in a field that I am conditioning, I use COALESCE()... it just takes the thinking out of the equation.

    isnull({Product.Size}) or ({Product.Size} <> "xlrg")

    becomes

    COALESCE(Product.Size, "whatever-you-want-it-to-be-if-the-field-is-null")

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.