Home » Developer & Programmer » Reports & Discoverer » Count Distinct PL/SQL
Count Distinct PL/SQL [message #255963] Thu, 02 August 2007 10:17 Go to next message
Messages: 22
Registered: March 2006
Junior Member
I'm sure this'll be an easy question for someone. I have a query that returns all the payments made to clients (one client can have multiple), for a given date range (usually fiscal). In the report I need to count the distinct clients, and given that I need all their individual payments, I can't do the COUNT(Distinct field) in the query. The summary column won't allow me to specify to count just distinct, and a formula column don't recognize COUNT as a PL/SQL function (because it's not, I know this, but I'm trying anything). Any ideas on how I can achieve this? If I haven't been clear enough, please let me know and I'll try again. Thanks.
Re: Count Distinct PL/SQL [message #256016 is a reply to message #255963] Thu, 02 August 2007 14:26 Go to previous message
Messages: 20847
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Try again Smile

I didn't understand why can't you use a FORMULA column; why wouldn't it recognize COUNT function?

As I've understood it, there are (at least) two parameters: par_start_date and par_end_date.

Your main query looks like
SELECT client_id, purchase_date, product_name, price
  FROM some_table
  WHERE purchase_date BETWEEN :par_start_date AND :par_end_date;

Number of distinct clients is independent of this query (at least I think so) and should be calculated separately: either using another query (which doesn't have to be joined to the main one), or using a formula column. Whichever it is, query would be the same (formula column example):
  l_cnt_clients NUMBER;
    INTO l_cnt_clients
    FROM some_table
    WHERE purchase_date BETWEEN :par_start_date AND :par_end_date;

  RETURN (l_cnt_clients);

If I misunderstood the question, could you (really) try again? This time with sample data and expected result. Please, do not upload XLS or DOC files; do it using proper formatting with the [code] tags.
Previous Topic: how to connect discov4 to oracle 10g
Next Topic: Reports - lines are not printed
Goto Forum:

Current Time: Tue Oct 25 18:26:54 CDT 2016

Total time taken to generate the page: 0.10286 seconds