RE: 2nd Req URGENT pls help- needed (deletes)

Hello Salu,

I'm curious about how the join_date is stored. Is it a date column or varchar2? If it's a date, do a to_char(join_date, 'DD-MON-YYYY') to make sure it's not actually '0001' or '1901' rather than '2001'.

Diana

-----Original Message-----
From: salu Ullah [mailto:salu_ullah_at_hotmail.com] Sent: Wednesday, December 06, 2000 6:11 PM To: Multiple recipients of list ORACLE-L Subject: RE: 2nd Req URGENT pls help- needed (deletes)

Hello Diana

I ran the cursor u send me but still its not deleting any records.

after running the cursor i got :

Deleted 0 rows.
Total Rows Deleted from Mem Check: 0

PL/SQL procedure successfully completed.

Both data has same three columns with same data type. For more info below are the data from tow tables

Temp_rc table

MEM_ID RT_C RM_CODE
------ ---- --------
001640 RACK A
001640 RACK A1D1O
001640 RACK B
001640 RACK C
001640 RACK D
001640 RACK K
001640 RACK L Mem_check table

MEM_ID JOIN_DATE RT_C RM_CODE A

------ --------- ---- -------- -
001640 04-JAN-01 RACK A        B
001640 01-APR-01 RACK L        B
001640 02-APR-01 RACK A        B
001640 03-APR-01 RACK A        B
001640 04-APR-01 RACK D        B
001640 05-APR-01 RACK L        B
001640 22-APR-01 RACK A        B
001640 23-APR-01 RACK B        B
001640 24-APR-01 RACK A1D10    B
001640 26-APR-01 RACK A        B
001640 27-APR-01 RACK A        B
001640 28-APR-01 RACK B        B
001640 29-APR-01 RACK A        B
001640 30-APR-01 RACK D        B

Want to delete rows from mem_check where it matches values from temp_rc and join_date > '31-JAN-00'

Would appreciate any help.

Thanks

Salman

>From: Diana Duncan <Diana_at_filefrenzy.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: 2nd Req URGENT pls help- needed (deletes)
>Date: Wed, 06 Dec 2000 13:15:37 -0800
>
>Salu,
>
>You have a few problems with your program, although you are on the right
>track. You want to do your deletes in chunks to avoid running out of
>rollback, which is good.
>
>Your problems are:
>1) you are counting the number of rows in your cursor (7) not the number of
>rows you are deleting
>2) you are only deleting 500 rows for each entry in your temp table,
>instead
>of deleting all of the rows that meet the requirements.
>3) is your 'join_date' column an actual date? If not, you need to cast it
>to a date to do date comparisons.
>
>How about something like the following (untested, off the top of my head,
>the usual caveats):
>
>declare
> v_join_date date := to_date('31-DEC-2000', 'DD-MON-YYYY');
> v_count number(10) := 0;
>begin
> loop
> delete from mem_check
> where (mem_id, rt_code, rm_code) in (select mem_id, rt_code,
>rm_code from temp_rc)
> and to_date(join_date) > v_join_date
> and rownum <= 500;
> v_count := v_count + sql%rowcount;
> dbms_output.put_line('Deleted ' || sql%rowcount || '
>rows.');
> commit;
> exit when sql%notfound;
Received on Thu Dec 07 2000 - 12:00:00 CST