| Cursors [message #2413] |
Fri, 12 July 2002 13:21  |
spat
Messages: 10
Registered: March 2002
|
Junior Member |
|
|
I recently learned to use cursors for updates, and large inserts. I need some help getting the cursor below to generate the final results.
The cursor
TRUNCATE TABLE PROD_TA_VOUCHER_HRS_TEMP;
declare
cursor s1 is SELECT A.PAYNO , to_char(A.START_DATE, 'mm/dd/yyyy'), SUM(A.ELAPSED_TIME)
FROM VOUCHER A
WHERE to_char(A.START_DATE, 'mm/dd/yyyy') >= to_char(sysdate - 365 , 'mm/dd/yyyy')
GROUP BY A.PAYNO, to_char(A.START_DATE, 'mm/dd/yyyy');
my_badge VOUCHER.PAYNO%type;
my_sdt VOUCHER.START_DATE%type;
my_ttl VOUCHER.ELAPSED_TIME%type;
begin
open s1;
loop
fetch s1 into my_badge, my_sdt, my_ttl;
exit when s1%notfound;
INSERT INTO PROD_TA_VOUCHER_HRS_TEMP ( PAYNO, START_DATE, TTL ) VALUES
(my_badge, my_sdt, my_ttl );
commit;
end loop;
close s1;
end;
/
Voucher table contains data such as
Badge Start Date Elasped Time
H2230 05/02/2002 8:00 am 1.2
H2230 05/02/2002 9:20 am 4.2
H2230 05/02/2002 3:20 pm 4.2
H2231 etc.
Rusults required are
Badge Start Date Ttl
H2230 05/02/2002 9.6
H2231 05/02/2002 7.5
etc.
With the cursor above I get
Badge Start Date Ttl
H2230 05/02/2002 8:00 am 1.2
H2230 05/02/2002 9:20 am 4.2
H2230 05/02/2002 3:20 pm 4.2
H2231 etc.
Any help that is provided would be greatly appreciated.
Thank you.
|
|
|
|
| Re: Cursors [message #2414 is a reply to message #2413] |
Fri, 12 July 2002 13:52  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
No need for a cursor here - if you are getting 'unable to extend' rollback segment errors, then make them the right size (larger) for the types of transactions you do.
This whole routine is simply:
insert into prod_ta_voucher_hrs_temp
(payno, start_date, ttl)
select payno, trunc(start_date), sum(elapsed_time)
from voucher
where start_date >= add_months(sysdate, -12)
group by payno, trunc(start_date);
Note the removal of:
1) the alias
2) the to_char conversions
and the addition of:
1) trunc of the date
2) add_months (to handle leap years correctly)
|
|
|
|
| Re: Cursors [message #2424 is a reply to message #2413] |
Mon, 15 July 2002 07:42 |
spat
Messages: 10
Registered: March 2002
|
Junior Member |
|
|
Thank you Todd
I am not an admin in order to change the rollback segment. But the trunc in the cursor worked perfectly for me.
Thanks again.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
