Re: Discoverer - Count(Distinct)

i don't know if this is what you want but in the tools options is a setting for distinct rows only - it only applies to a particular worksheet.

Mark

Jeanne Kasulke wrote:
>
> 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 Fri Jun 19 1998 - 05:32:44 CEST