Crosstab Distinct Count Problem

juan436585

Ok so I have encountered a problem that I cannot seem to find a way around and hopefully somebody can help me or give me an idea. I will try to explain the problem best i can.

So i have a dataset with a query and in the select statement i have a part like this:

( CASE WHEN field1 <> 0 THEN field2 ELSE NULL END ) AS field3

Now, in my Data Cube i have field3 set as a COUNTDISTINCT. The problem i seem to be having is that in my crosstab my counts seem to be always be off by 1 and i believe it is because it is still counts the null as 1.

Any ideas on how to get around this or maybe do this a different way?

Any help is greatly appreciated!!

jrep

That sounds an awful lot like this:<br />
<br />
<a class='bbc_url' href='http://rwijk.blogspot.com/2009/01/count-distinct.html'>About Oracle: COUNT DISTINCT</a><br />
<br />
The SQL in the examples there is probably not the same as yours, but the underlying point might apply: if you count a table (which was created by selecting distinct), then rows with NULLs still exist and get counted. You can only avoid counting the NULLs by counting(distinct) the possibly null bearing column directly.