Discoverer - Count(Distinct)

From: Jeanne Kasulke <jkasulke_at_asq.org>
Date: Wed, 17 Jun 1998 09:46:33 -0500
Message-ID: <3587D6C9.1C24FE78_at_asq.org>

[Quoted] Can someone please help me determine a way to make a count(distinct) function available in Discoverer. The sql I'm attempting to generate in it's simplest form is:

   select count(distinct address_book_number) from mart.transaction_fact;

Running this sql in Brio Query and in SQL*Plus returns a result in 2 or so minutes. When I create it in Discoverer, it takes upwards of 8 hours. I believe this is due to the "in line views" Discoverer creates to facilitate drilling.

I created a registered plsql function to perform the same as follows: return integer
is
cd_cudt integer;
begin
select count(distinct address_book_number) into cd_cust from mart.transaction_fact;
return cd_cust;
end count_customer;

This gives a result in 10 minutes - which is acceptable, but isn't dynamic enough to support ad hoc queries. What can I do???

This data element is contained in my fact table and there are multiple dimensions available for query conditions. The data is dealing with customers and orders - so I'd like to be able to say that Joe Blow had 20 orders with us for 5 different products - he should be counted as one person regardless of what product I'm looking at.

Thanks for any suggestions anyone has. Received on Wed Jun 17 1998 - 16:46:33 CEST