Evaluating zero and null values 1

[sboyle1]

I have a report doing some strange things regarding evaluating a zero or null value. The report uses a left outer join to join an inventory table to a bill of materials (BOM) table. There are some records that have a matching BOM record, and some that don't. I have a boolean parameter ({?Zero_Quantity}) that asks the user if they want to show records with BOM quantities equal to 0.

Because of the outer join, I need to suppress records with null values as well as 0 values. So, I'm conditionally suppressing the detail record using the formula below:

{?Zero_Quantity}=False
and
(
{BOM.Quantity}=0
or
isnull({BOM.Quantity})
)

This formula only suppresses the BOM.Quantity = 0, effectively ignoring the isnull evaluation. When I change the formula to the following, everything works:

{?Zero_Quantity}=False
and
(
isnull({BOM.Quantity})
or
{BOM.Quantity}=0
)

Shouldn't the two formulas work exactly the same?

 

[dgillz]

Under file, report options, make sure the "convert null field values to default" option is checked, and run your report again. You most likely will not have to check for nulls this way.

Also, always check for nulls first, that is why your second formula works. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[sboyle1]

Thanks for the reply. I'd never used the "convert null field values to default" -- it makes this sort of issue go away, which is nice. The real question on this is WHY you have to check for null values first. Shouldn't the formula evaluate every part of the "or" statement? Or does it essentially "break" once you try to evaluate a null field as a value?

 

[synapsevampire]

Eees ah Booooog...methinks

It isn't always the case with CR that it falls out of a test condition (formula) if null isn't 1st, but to be safe (as dgillz appropriately points out), test for it first, it does fall out unexpectedly.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

Crystal always evaluates from left to right....the formula fails when it encounters a null if you haven't tested for it first Jim Broadbent

 

[synapsevampire]

Well Hi Jim, haven't heard from you for a bit - Happy New Years, good to see you back, and I trust that it's going well for you.

CR seems very flakey with null testing, so I avoid it whenever possible. I generally create a SQL Expression to check for null or acomplish is null testing within a View/SP.

Try checking for isnull() in a CR Running Total evaluation...very annoying, or build a record selection formula that has an isnull() as the 2nd check and it will construct it correctly, pass it through to the database, then filter it out afterwards...

That's not left to right logic processing, that's at best, poorly conceived...

Avoid isnull() in CR, it's unpredictable, and inconsistent with how IS NULL functions in any other language I've worked with.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dgillz]

Jim,

SV Is right - it is good to have you back.

I also agree with SV's distrust of isnull() and have had my own mixed results using it. I have had to write formulas such as length(trim({FieldName}))=0 at times to test for nulls. As a result, I try to set the "convert null values to default" checkbox, but that does not always work. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[Ngolem]

Thanks...it is good to be back....I am actually in Thailand right now...taking a haitus from the techie world....teaching english to gifted science high school students...sort of a paid vacation...but too damn hot!!!

I don't use Isnull() as a test rather i use Not Isnull() I found that to be much more reliable Jim Broadbent

 

[synapsevampire]

Thailand!

Very nice, Jim, if your intent was to make us all incredibly envious, you've scored here.

I just don't use isnull in CR, every database has a reliable consistent means of checking nulls, CR is just plain wrong...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com