Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL - Adding data to a cursor multiple times
PL/SQL - Adding data to a cursor multiple times [message #254640] Fri, 27 July 2007 06:06 Go to next message
m1ke
Messages: 10
Registered: July 2007
Junior Member
What i want to do is something along the lines of this.. one cursor (pld_cur) gets some records from a table. Then I want use some of the valaues from the records it retrieves and use them to get data for another cursor, from another table..

But what is happening is the returned cursor only has data as if it was only run for one record from pld_cur. What I want to do is open the second cursor (csr_return) to get data based on the values from the current row from pld_cur.. then go to the next row of pld_cur and add the new data it gets to csr_Return. So csr_Return has the records from every run of the loop.

PROCEDURE MyProcedure
    		  ( csr_Return       	OUT   	REF_CURSOR)
   	IS
      l_sql                      VARCHAR2(32000);
    CURSOR pld_cur IS
        SELECT  customer_id
                , startdate
                , enddate
        from PLD_TABLE pld
        ;

   	BEGIN
   	
   FOR pld_rec IN pld_cur
   LOOP        
        l_sql := '
                    SQL STATEMENT TO GET DATA BASED ON CURSOR PARAMETERS
			';
        OPEN csr_return FOR l_sql using pld_rec.startdate
                                        , pld_rec.enddate
                                        , pld_rec.customer_id
                                        , pld_rec.startdate
                                        ;        
   END LOOP;
		END MyProcedure;


So what i think is happening is either everytime csr_return is opened, it overwrites any existing data it previously contained OR the first cursor (pld_cur) is not going through each record..

Any ideas?
Thanks
Re: PL/SQL - Adding data to a cursor multiple times [message #254645 is a reply to message #254640] Fri, 27 July 2007 06:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Your OUT parameter gets a different value (i.e. pointer to a cursor) every iteration of the loop.
Only after finishing the loop, the out-parameter is returned to the calling procedure, hence only the last ref-cursor is reachable from there.
Re: PL/SQL - Adding data to a cursor multiple times [message #254648 is a reply to message #254645] Fri, 27 July 2007 06:29 Go to previous messageGo to next message
m1ke
Messages: 10
Registered: July 2007
Junior Member
Aah, ok. Thanks for the reply.. what can i do to my
code to compensate for this?

Thanks
Re: PL/SQL - Adding data to a cursor multiple times [message #254650 is a reply to message #254648] Fri, 27 July 2007 06:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rewrite your query to join the cursor-sql to the dynamic sql part
Re: PL/SQL - Adding data to a cursor multiple times [message #254657 is a reply to message #254650] Fri, 27 July 2007 06:45 Go to previous messageGo to next message
m1ke
Messages: 10
Registered: July 2007
Junior Member
Ok, does this mean that I can no longer use a FOR LOOP here and use two separate cursors -- e.g. do i have to combine both SQL's into one (very tricky here) or is there a way i can do it still using two separate cursors? Thanks
Re: PL/SQL - Adding data to a cursor multiple times [message #254671 is a reply to message #254657] Fri, 27 July 2007 07:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why is it so tricky? I don't see any tablenames or columnnames.
Looks pretty straightforward to me.

Maybe you can post the 2nd query (if it is not too long)
Re: PL/SQL - Adding data to a cursor multiple times [message #254676 is a reply to message #254671] Fri, 27 July 2007 08:11 Go to previous messageGo to next message
m1ke
Messages: 10
Registered: July 2007
Junior Member
Ok -- its quite complicated, thus the reason i decided to go about this using two cursors. When i have tried to combine the SQL before, the values from the dynamic_sql come up with incorrect figures unlike this way..

SELECT client_id 
	  , TO_CHAR(TRUNC (invoice_date, 'MM'), 'DD-MON-YYYY') invoice_Date
	  , value1
	  , value2
	  , value3
	  , value4
	  , sum(cnt) annual_visits     --so sum all the visits for the combined value1/value2
									--otherwise group-by will group some!
	  , age_at_event max_age
from
	(
		select client_id
			 , customer_id
			 , invoice_date
			 , max(cnt) cnt
			 , value1
			 , value2
			 , value3
			 , value4
			 , age_at_event
		from
		(
			 select client_id, customer_id, invoice_date
					--count no of weeks (visits) customer has made in 12 month period
					, count(week)
						 over(partition by customer_id, client_id
							  order by invoice_date
							  range between invoice_date-add_months(invoice_date,-11) preceding 
							  and current row) cnt
					, value1
					, value2
					, value3
					, value4
					, age_at_event
			from
			(
					select client_id
						, customer_id
						, invoice_date
						--week number (null if customer already has a visit in that week
						, decode(lag(to_char(invoice_date,'MM')||to_char(invoice_date,'W')) 
								over (partition by customer_id, client_id
										order by invoice_date),
										 to_char(invoice_date,'MM')||to_char(invoice_date,'W'), null,
										 to_char(invoice_date,'MM')||to_char(invoice_date,'W')) week
					   , NVL(d.value1, 'NB') value1
					   , NVL(d.value2, 'nnn') value2
					   , d.value3
					   , d.value4
					   , max(d.age_at_event) over (partition by customer_id, client_id) age_at_event								   
						from invoices d

						---Current_dates! 12 month period from start_date.
						where invoice_date between add_months(to_date(:p_1startdate, 'DD-MON-YYYY'), -11)
							and to_date(:p2_enddate, 'DD-MON-YYYY')
						and client_id = :p3_client_id
			)
		)
		group by client_id, customer_id, invoice_date
								   , value1
								   , value2
								   , value3
								   , value4
								   , age_at_event
	) 
where trunc(invoice_date, 'mm') = to_date(:p4_startdate, 'DD-MON-YYYY')    --just get current-date

group by client_id
		, value1
		, value2
		, value3
		, value4
		, age_at_event
		, trunc(invoice_date , 'mm')
order by 1


If you can make anything of that i'll be impressed... Ideally i like not to change that..
is there a way i could still do it using two cursors or similar, perhaps using a function.. in the end, i just need to return a cursor which contains the full results, or append the cursor somehow after each loop.. is this possible?

Thanks a lot, i really appreciate the help

[Updated on: Fri, 27 July 2007 08:17]

Report message to a moderator

Re: PL/SQL - Adding data to a cursor multiple times [message #254727 is a reply to message #254640] Fri, 27 July 2007 13:30 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
if you want to use two cursors, use something like this

declare

cursor loop1 is
SELECT  customer_id
                , startdate
                , enddate
        from PLD_TABLE pld;

cursor loop2(cust_id in number,
             st_date in date,
             end_date in date) is
select col1,col2,col3
from my_table
where customer_id = cust_id
and the_date between st_date and end_date;

begin

for pnt in loop1 loop
  for rec in loop2(pnt.customer_id,pnt,startdate,pnt.enddate) loop
   .... what ever processing you want to do
  end loop;
end loop;
end;
Re: PL/SQL - Adding data to a cursor multiple times [message #254787 is a reply to message #254676] Sat, 28 July 2007 05:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
join invoices to pld_table
Re: PL/SQL - Adding data to a cursor multiple times [message #254825 is a reply to message #254640] Sat, 28 July 2007 18:03 Go to previous message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
I know this is not what you asked for, but I don't think your query makes any sense. Let's say you have the following data (I added the breaks just to indicate where data logically breaks and repeats. I inserted the data by the autonomous block at the end of this post. The dates repeat. customer_id and value1 "increment"):
SQL> select * from invoices order by customer_id, value1, invoice_date;

CLIENT_ID CUSTOMER_ID INVOICE_D    VALUE1    VALUE2    VALUE3    VALUE4 AGE_AT_EVENT
--------- ----------- --------- --------- --------- --------- --------- ------------
        1           1 02-JAN-06         1         2         3         4            1
        1           1 03-JAN-06         1         2         3         4            1
        1           1 17-JAN-06         1         2         3         4            1
        1           1 18-JAN-06         1         2         3         4            1
        1           1 02-FEB-06         1         2         3         4            1
        1           1 03-FEB-06         1         2         3         4            1
        1           1 17-FEB-06         1         2         3         4            1
        1           1 18-FEB-06         1         2         3         4            1
        1           1 29-APR-06         1         2         3         4            1
        1           1 30-APR-06         1         2         3         4            1
        1           1 21-AUG-06         1         2         3         4            1
        1           1 22-AUG-06         1         2         3         4            1
        1           1 02-JAN-07         1         2         3         4            1
        1           1 03-JAN-07         1         2         3         4            1
        1           1 17-JAN-07         1         2         3         4            1
        1           1 18-JAN-07         1         2         3         4            1
        1           1 02-FEB-07         1         2         3         4            1
        1           1 03-FEB-07         1         2         3         4            1
        1           1 17-FEB-07         1         2         3         4            1
        1           1 18-FEB-07         1         2         3         4            1

        1           1 02-JAN-06         2         2         3         4            1
        1           1 03-JAN-06         2         2         3         4            1
        1           1 17-JAN-06         2         2         3         4            1
        1           1 18-JAN-06         2         2         3         4            1
        1           1 02-FEB-06         2         2         3         4            1
        1           1 03-FEB-06         2         2         3         4            1
        1           1 17-FEB-06         2         2         3         4            1
        1           1 18-FEB-06         2         2         3         4            1
        1           1 29-APR-06         2         2         3         4            1
        1           1 30-APR-06         2         2         3         4            1
        1           1 21-AUG-06         2         2         3         4            1
        1           1 22-AUG-06         2         2         3         4            1
        1           1 02-JAN-07         2         2         3         4            1
        1           1 03-JAN-07         2         2         3         4            1
        1           1 17-JAN-07         2         2         3         4            1
        1           1 18-JAN-07         2         2         3         4            1
        1           1 02-FEB-07         2         2         3         4            1
        1           1 03-FEB-07         2         2         3         4            1
        1           1 17-FEB-07         2         2         3         4            1
        1           1 18-FEB-07         2         2         3         4            1

        1           2 02-JAN-06         1         2         3         4            1
        1           2 03-JAN-06         1         2         3         4            1
        1           2 17-JAN-06         1         2         3         4            1
        1           2 18-JAN-06         1         2         3         4            1
        1           2 02-FEB-06         1         2         3         4            1
        1           2 03-FEB-06         1         2         3         4            1
        1           2 17-FEB-06         1         2         3         4            1
        1           2 18-FEB-06         1         2         3         4            1
        1           2 29-APR-06         1         2         3         4            1
        1           2 30-APR-06         1         2         3         4            1
        1           2 21-AUG-06         1         2         3         4            1
        1           2 22-AUG-06         1         2         3         4            1
        1           2 02-JAN-07         1         2         3         4            1
        1           2 03-JAN-07         1         2         3         4            1
        1           2 17-JAN-07         1         2         3         4            1
        1           2 18-JAN-07         1         2         3         4            1
        1           2 02-FEB-07         1         2         3         4            1
        1           2 03-FEB-07         1         2         3         4            1
        1           2 17-FEB-07         1         2         3         4            1
        1           2 18-FEB-07         1         2         3         4            1

        1           2 02-JAN-06         2         2         3         4            1
        1           2 03-JAN-06         2         2         3         4            1
        1           2 17-JAN-06         2         2         3         4            1
        1           2 18-JAN-06         2         2         3         4            1
        1           2 02-FEB-06         2         2         3         4            1
        1           2 03-FEB-06         2         2         3         4            1
        1           2 17-FEB-06         2         2         3         4            1
        1           2 18-FEB-06         2         2         3         4            1
        1           2 29-APR-06         2         2         3         4            1
        1           2 30-APR-06         2         2         3         4            1
        1           2 21-AUG-06         2         2         3         4            1
        1           2 22-AUG-06         2         2         3         4            1
        1           2 02-JAN-07         2         2         3         4            1
        1           2 03-JAN-07         2         2         3         4            1
        1           2 17-JAN-07         2         2         3         4            1
        1           2 18-JAN-07         2         2         3         4            1
        1           2 02-FEB-07         2         2         3         4            1
        1           2 03-FEB-07         2         2         3         4            1
        1           2 17-FEB-07         2         2         3         4            1
        1           2 18-FEB-07         2         2         3         4            1

80 rows selected.
If you run the inner part of your query, you get this:
SQL> SELECT client_id,
  2         customer_id,
  3         invoice_date
  4         --week number (null if customer already has a visit in that week
  5        ,decode(lag(to_char(invoice_date, 'MM') || to_char(invoice_date, 'W')) over(PARTITION BY customer_id, client_id ORDER BY invoice_date),
  6                to_char(invoice_date, 'MM') || to_char(invoice_date, 'W'), NULL,
  7                to_char(invoice_date, 'MM') || to_char(invoice_date, 'W')) week,
  8         NVL(d.value1, 1) value1,
  9         NVL(d.value2, 2) value2,
 10         d.value3,
 11         d.value4,
 12         MAX(d.age_at_event) over(PARTITION BY customer_id, client_id) age_at_event
 13  FROM   invoices d
 14  ---Current_dates! 12 month period from start_date.
 15  WHERE  invoice_date BETWEEN
 16         add_months(to_date('01-JAN-2007', 'DD-MON-YYYY'), -11) AND
 17         to_date('28-FEB-2007', 'DD-MON-YYYY')
 18  AND    client_id = 1
 19  ORDER BY customer_id, value1, invoice_date;

CLIENT_ID CUSTOMER_ID INVOICE_D WEE    VALUE1    VALUE2    VALUE3    VALUE4 AGE_AT_EVENT
--------- ----------- --------- --- --------- --------- --------- --------- ------------
        1           1 02-FEB-06 021         1         2         3         4            1
        1           1 03-FEB-06             1         2         3         4            1
        1           1 17-FEB-06 023         1         2         3         4            1
        1           1 18-FEB-06             1         2         3         4            1
        1           1 29-APR-06 045         1         2         3         4            1
        1           1 30-APR-06             1         2         3         4            1
        1           1 21-AUG-06 083         1         2         3         4            1
        1           1 22-AUG-06 084         1         2         3         4            1
        1           1 02-JAN-07 011         1         2         3         4            1
        1           1 03-JAN-07             1         2         3         4            1
        1           1 17-JAN-07 013         1         2         3         4            1
        1           1 18-JAN-07             1         2         3         4            1
        1           1 02-FEB-07 021         1         2         3         4            1
        1           1 03-FEB-07             1         2         3         4            1
        1           1 17-FEB-07 023         1         2         3         4            1
        1           1 18-FEB-07             1         2         3         4            1
        1           1 02-FEB-06             2         2         3         4            1
        1           1 03-FEB-06             2         2         3         4            1
        1           1 17-FEB-06             2         2         3         4            1
        1           1 18-FEB-06             2         2         3         4            1
        1           1 29-APR-06             2         2         3         4            1
        1           1 30-APR-06             2         2         3         4            1
        1           1 21-AUG-06             2         2         3         4            1
        1           1 22-AUG-06             2         2         3         4            1
        1           1 02-JAN-07             2         2         3         4            1
        1           1 03-JAN-07             2         2         3         4            1
        1           1 17-JAN-07             2         2         3         4            1
        1           1 18-JAN-07             2         2         3         4            1
        1           1 02-FEB-07             2         2         3         4            1
        1           1 03-FEB-07             2         2         3         4            1
        1           1 17-FEB-07             2         2         3         4            1
        1           1 18-FEB-07             2         2         3         4            1
        1           2 02-FEB-06 021         1         2         3         4            1
        1           2 03-FEB-06             1         2         3         4            1
        1           2 17-FEB-06 023         1         2         3         4            1
        1           2 18-FEB-06             1         2         3         4            1
        1           2 29-APR-06 045         1         2         3         4            1
        1           2 30-APR-06             1         2         3         4            1
        1           2 21-AUG-06 083         1         2         3         4            1
        1           2 22-AUG-06 084         1         2         3         4            1
        1           2 02-JAN-07 011         1         2         3         4            1
        1           2 03-JAN-07             1         2         3         4            1
        1           2 17-JAN-07 013         1         2         3         4            1
        1           2 18-JAN-07             1         2         3         4            1
        1           2 02-FEB-07 021         1         2         3         4            1
        1           2 03-FEB-07             1         2         3         4            1
        1           2 17-FEB-07 023         1         2         3         4            1
        1           2 18-FEB-07             1         2         3         4            1
        1           2 02-FEB-06             2         2         3         4            1
        1           2 03-FEB-06             2         2         3         4            1
        1           2 17-FEB-06             2         2         3         4            1
        1           2 18-FEB-06             2         2         3         4            1
        1           2 29-APR-06             2         2         3         4            1
        1           2 30-APR-06             2         2         3         4            1
        1           2 21-AUG-06             2         2         3         4            1
        1           2 22-AUG-06             2         2         3         4            1
        1           2 02-JAN-07             2         2         3         4            1
        1           2 03-JAN-07             2         2         3         4            1
        1           2 17-JAN-07             2         2         3         4            1
        1           2 18-JAN-07             2         2         3         4            1
        1           2 02-FEB-07             2         2         3         4            1
        1           2 03-FEB-07             2         2         3         4            1
        1           2 17-FEB-07             2         2         3         4            1
        1           2 18-FEB-07             2         2         3         4            1

64 rows selected.
A quick visual count of weeks indicates there are a total of 18 different weeks in which there were visits, 9 per customer_id, but if you ignore counts from February of 07, then there are 7, 5 below that start date and 2 after. Your query, however, yeilds results that cannot possibly make sense.
SQL> SELECT client_id,
  2         TO_CHAR(TRUNC(invoice_date, 'MM'), 'DD-MON-YYYY') invoice_Date,
  3         value1,
  4         value2,
  5         value3,
  6         value4,
  7         SUM(cnt) annual_visits --so sum all the visits for the combined value1/value2
  8         --otherwise group-by will group some!
  9        ,age_at_event max_age
 10  FROM   (SELECT client_id,
 11                 customer_id,
 12                 invoice_date,
 13                 MAX(cnt) cnt,
 14                 value1,
 15                 value2,
 16                 value3,
 17                 value4,
 18                 age_at_event
 19          FROM   (SELECT client_id,
 20                         customer_id,
 21                         invoice_date
 22                         --count no of weeks (visits) customer has made in 12 month period
 23                        ,COUNT(week) over(PARTITION BY customer_id, client_id ORDER BY invoice_date RANGE BETWEEN invoice_date - add_months(invoice_date, -11) preceding AND CURRENT ROW) cnt,
 24                         value1,
 25                         value2,
 26                         value3,
 27                         value4,
 28                         age_at_event
 29                  FROM   (SELECT client_id,
 30                                 customer_id,
 31                                 invoice_date
 32                                 --week number (null if customer already has a visit in that week
 33                                ,decode(lag(to_char(invoice_date, 'MM') ||
 34                                            to_char(invoice_date, 'W'))
 35                                        over(PARTITION BY customer_id,
 36                                             client_id ORDER BY invoice_date),
 37                                        to_char(invoice_date, 'MM') ||
 38                                        to_char(invoice_date, 'W'),
 39                                        NULL,
 40                                        to_char(invoice_date, 'MM') ||
 41                                        to_char(invoice_date, 'W')) week,
 42                                 NVL(d.value1, 1) value1,
 43                                 NVL(d.value2, 2) value2,
 44                                 d.value3,
 45                                 d.value4,
 46                                 MAX(d.age_at_event) over(PARTITION BY customer_id, client_id) age_at_event
 47                          FROM   invoices d
 48                          ---Current_dates! 12 month period from start_date.
 49                          WHERE  invoice_date BETWEEN
 50                                 add_months(to_date('01-JAN-2007', 'DD-MON-YYYY'),
 51                                            -11) AND
 52                                 to_date('28-FEB-2007', 'DD-MON-YYYY')
 53                          AND    client_id = 1))
 54          GROUP  BY client_id,customer_id,invoice_date,value1,value2,value3,value4,age_at_event)
 55  WHERE  trunc(invoice_date, 'mm') = to_date('01-JAN-2007', 'DD-MON-YYYY') --just get current-date
 56  GROUP  BY client_id,value1,value2,value3,value4,age_at_event,trunc(invoice_date, 'mm')
 57  ORDER  BY 1;

CLIENT_ID INVOICE_DAT    VALUE1    VALUE2    VALUE3    VALUE4 ANNUAL_VISITS   MAX_AGE
--------- ----------- --------- --------- --------- --------- ------------- ---------
        1 01-JAN-2007         1         2         3         4            44         1
        1 01-JAN-2007         2         2         3         4            44         1
By "walking" your way out of the inline views, you can easily see where the 44 comes from, it just doesn't make any sense. There are a few other things that don't seem to me to make sense. What is the functional reason for having an invoice date of 01-JAN-2007. You have two invoices in January of 2007. One would think that is what you want to see.

It seems to me that your query should be much simpler:
SQL> SELECT i.*,
  2         (SELECT COUNT(DISTINCT trunc(invoice_date, 'W'))
  3          FROM   invoices d
  4          ---Current_dates! 1 year minus 1 day from invoice date.
  5          WHERE  d.invoice_date >= add_months(i.invoice_date, -12) + 1
  6          AND    d.invoice_date < i.invoice_date
  7          AND    d.client_id = i.client_id) visits
  8  FROM   invoices i
  9  WHERE  trunc(i.invoice_date, 'mm') = to_date('01-JAN-2007', 'DD-MON-YYYY')
 10  AND    i.client_id = 1;

CLIENT_ID CUSTOMER_ID INVOICE_D    VALUE1    VALUE2    VALUE3    VALUE4 AGE_AT_EVENT    VISITS
--------- ----------- --------- --------- --------- --------- --------- ------------ ---------
        1           1 02-JAN-07         1         2         3         4            1         7
        1           2 02-JAN-07         1         2         3         4            1         7
        1           1 03-JAN-07         1         2         3         4            1         7
        1           2 03-JAN-07         1         2         3         4            1         7
        1           1 02-JAN-07         2         2         3         4            1         7
        1           2 02-JAN-07         2         2         3         4            1         7
        1           1 03-JAN-07         2         2         3         4            1         7
        1           2 03-JAN-07         2         2         3         4            1         7
        1           1 17-JAN-07         1         2         3         4            1         7
        1           2 17-JAN-07         1         2         3         4            1         7
        1           1 18-JAN-07         1         2         3         4            1         7
        1           2 18-JAN-07         1         2         3         4            1         7
        1           1 17-JAN-07         2         2         3         4            1         7
        1           2 17-JAN-07         2         2         3         4            1         7
        1           1 18-JAN-07         2         2         3         4            1         7
        1           2 18-JAN-07         2         2         3         4            1         7

16 rows selected.
And if you really do want the results you are currently not getting:
SQL> SELECT client_id,
  2         value1,
  3         value2,
  4         value3,
  5         value4,
  6         age_at_event,
  7         trunc(invoice_date, 'mm'),
  8         MAX(visits) visits
  9  FROM   (SELECT i.*,
 10                 (SELECT COUNT(DISTINCT trunc(invoice_date, 'W'))
 11                  FROM   invoices d
 12                  ---Current_dates! 1 year minus 1 day from invoice date.
 13                  WHERE  d.invoice_date >= add_months(i.invoice_date, -12) + 1
 14                  AND    d.invoice_date < i.invoice_date
 15                  AND    d.client_id = i.client_id) visits
 16          FROM   invoices i
 17          WHERE  trunc(i.invoice_date, 'mm') = to_date('01-JAN-2007', 'DD-MON-YYYY')
 18          AND    i.client_id = 1)
 19  GROUP  BY client_id,
 20            value1,
 21            value2,
 22            value3,
 23            value4,
 24            age_at_event,
 25            trunc(invoice_date, 'mm');

CLIENT_ID    VALUE1    VALUE2    VALUE3    VALUE4 AGE_AT_EVENT TRUNC(INV    VISITS
--------- --------- --------- --------- --------- ------------ --------- ---------
        1         1         2         3         4            1 01-JAN-07         7
        1         2         2         3         4            1 01-JAN-07         7
Then with a populated pld_table
SQL> SELECT customer_id,
  2         startdate
  3  FROM pld_table pld;

CUSTOMER_ID STARTDATE
----------- ---------
          1 01-JAN-07
You can do this:
SQL> SELECT client_id,
  2         value1,
  3         value2,
  4         value3,
  5         value4,
  6         age_at_event,
  7         trunc(invoice_date, 'mm'),
  8         MAX(visits) visits
  9  FROM   (SELECT i.*,
 10                 (SELECT COUNT(DISTINCT trunc(invoice_date, 'W'))
 11                  FROM   invoices d
 12                  ---Current_dates! 1 year minus 1 day from invoice date.
 13                  WHERE  d.invoice_date >= add_months(i.invoice_date, -12) + 1
 14                  AND    d.invoice_date < i.invoice_date
 15                  AND    d.client_id = i.client_id) visits
 16          FROM   invoices i
 17          WHERE  (i.client_id, trunc(i.invoice_date, 'mm')) IN (SELECT customer_id,
 18                                                                      trunc(startdate, 'mm')
 19                                                                FROM pld_table pld))
 20  GROUP  BY client_id,value1,value2,value3,value4,age_at_event,trunc(invoice_date, 'mm');

CLIENT_ID    VALUE1    VALUE2    VALUE3    VALUE4 AGE_AT_EVENT TRUNC(INV    VISITS
--------- --------- --------- --------- --------- ------------ --------- ---------
        1         1         2         3         4            1 01-JAN-07         7
        1         2         2         3         4            1 01-JAN-07         7





Data population code
declare
    procedure ins(p_start_date varchar2)
    is
        v_start_date date := to_date(p_start_date, 'mm/dd/rr');
    begin
        for i in 1..2 loop
            insert
            into invoices(client_id, customer_id, invoice_date, value1, value2, value3, value4, age_at_event)
            values       (        1,           1, v_start_date,      i,      2,      3,      4,            1);
            
            insert
            into invoices(client_id, customer_id, invoice_date, value1, value2, value3, value4, age_at_event)
            values       (        1,           2, v_start_date,      i,      2,      3,      4,            1);
            
            insert
            into invoices(client_id, customer_id, invoice_date    , value1, value2, value3, value4, age_at_event)
            values       (        1,           1, v_start_date + 1,      i,      2,      3,      4,            1);
            
            insert
            into invoices(client_id, customer_id, invoice_date    , value1, value2, value3, value4, age_at_event)
            values       (        1,           2, v_start_date + 1,      i,      2,      3,      4,            1);
        end loop;
    end;
begin
    delete invoices;
    ins('01/02/06');
    ins('01/17/06');
    ins('02/02/06');
    ins('02/17/06');
    ins('01/02/07');
    ins('01/17/07');
    ins('02/02/07');
    ins('02/17/07');
    ins('04/29/06');
    ins('08/21/06');
    commit;
end;

Previous Topic: Update the date upon record update
Next Topic: Cursor and sql statment
Goto Forum:
  


Current Time: Fri Dec 09 07:48:23 CST 2016

Total time taken to generate the page: 0.11681 seconds