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;
> end loop;
> dbms_output.put_line('Total rows deleted: ' || v_count);
>end;
>/
>
>-----Original Message-----
>Sent: Wednesday, December 06, 2000 2:39 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Hello djordjej
>
>I made some changes in my cursor...after running it is says no data found
>where as the temp table has 7 rows in it. Below is the cursor & the data
>in
>
>the temp table its reading from and also the output of the cursor.
>
>declare
>cursor c_mem is
>select mem_id, rt_code, rm_code
>from temp_rc;
>v_count number(10) :=0;
>v_deletes number(10) :=0;
>v_join_date mem_check.join_date%type :='31-DEC-00';
>begin
>for v_memdata in c_mem loop
>v_count := v_count + 1;
>v_deletes := v_deletes + 1;
>delete from mem_check
>where mem_id = v_memdata.mem_id
>and rt_code = v_memdata.rt_code
>and rm_code = v_memdata.rm_code
>and join_date > v_join_date;
>if sql%notfound then
>DBMS_OUTPUT.put_line ('Data not found');
>v_deletes := v_deletes;
>else
>v_deletes := v_deletes + 1;
>end if;
>if v_count = 500 then
>commit;
>v_count := 0;
>end if;
>end loop;
>commit;
>DBMS_OUTPUT.put_line ('Total Rows Deleted from Member Check: '||v_deletes);
>end;
>
>RESULT
>------
>
>Data not found
>Data not found
>Data not found
>Data not found
>Data not found
>Data not found
>Data not found
>Total Rows Deleted from Rate Check: 0
>
>PL/SQL procedure successfully completed.
>
>DATA in temp_rc
>
>MEM_ID RT_CD RM_CD
>------ ----- -----
>001640 RACK A
>001640 RACK A1D1O
>001640 RACK B
>001640 RACK C
>001640 RACK D
>001640 RACK K
>001640 RACK L
>
>Would really appreciate any help
>
>Thanks
>
>Salman
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Diana Duncan
> INET: Diana_at_fileFRENZY.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
Received on Wed Dec 06 2000 - 17:07:57 CST