Null values do not convert to 0 after case statement

[ccbarbeau]

Good morning,

I have a requirement where I must count the number of occurences for a date span and report the result in a table report. The results are then used in a query calculation.

My issue is that the count is sometimes NULL and that even though I have an If Then Else statement asking for a 0 to be returned when the value is NULL, I am still getting blanks in my results and therefore, my query calculations are not working properly.

I've tried multiplying my results by 1, changing my If Then Else statement to then (0) and then ('0'), using the coalesce function, doing my If Then Else statement directly in the package...I'm out of options at this point.

I'm using RS 8.4 off of a DB2 database.

Can anybody help? I need this report for the month-end  :-\

Thanks!!

[blom0344]

How do you present the results? You may confuse NULL values with non-existing intersections (for instance in a crosstab) A NULL value may exist for a dataitem in a row from a resultset, but if the row itself is not in the set , then applying functions or case constructs will not help..

[ccbarbeau]

The results are being presented in a list format. Each cell in the list is a singleton and each singleton is a factor in the query calculation.
I've gone into each query (there are about 75 or so) and made a data item for the query result (i.e. query has 'count of errors' data item that has the formula in the expression, and I added a data item that represents this result 'for report' in order to only have 1 result in my list)

[blom0344]

If a query singleton does NOT return a value, then do not mistake this to be a null value. In order to compare /apply case / use coalesce you need to have a row returned

[ccbarbeau]

Actually, the query itself returns no values with the filters I've added to it.

I need there to be a zero that I can calculate with in this case.

Do you have nay ideas as to how I can acheive this?

[blom0344]

Well, not if you are simply using singletons. In other cases I would build unions (2nd set bringing in 1 dummy record containing 0 for the count dataitem)

The alternative is making sure the model will always return a value, by either adding combinations in the model or in the underlying table

[ccbarbeau]

Unfortunately, I cannot change the source data.

I'm not sure doubling the number of queries is an option either, I'm afraid performance might become an issue.

Do you think that using an if statement in my equation might work? i.e.:

(if ([dataitem1] is not null) then ([dataitem1] else .....)+(if ([dataitem2] is not null) then ([dataitem2] else .....)+(if ([dataitem3] is not null) then ([dataitem3] else .....)

[blom0344]

You could give it a try, but performing an addition with a non existing value will probably yield an error..

[ccbarbeau]

Ok then, I guess I will have to go the union way. Can you please explain how you would go about this?

Well, not if you are simply using singletons. In other cases I would build unions (2nd set bringing in 1 dummy record containing 0 for the count dataitem)

[blom0344]

A normal table report would be associated with 1 page query. A union / singleton approach would be a problem I guess.  Personally I would define query number 76 to compute the overall addition of all aggregates in 1 go , but you may need to write your own SQL for this if the aggregates are coming from all parts of the model

[HalfBloodPrince]

Try this function.

coalesce ( expression_list )
Returns the first non-null argument (or null if all arguments are null). Requires two or more arguments in "expression_list".