Home » SQL & PL/SQL » SQL & PL/SQL » duplicates help !!!!!!!!!!!!
| duplicates help !!!!!!!!!!!! [message #18650] |
Fri, 08 February 2002 10:40  |
Rm69
Messages: 39
Registered: January 2002
|
Member |
|
|
Q) This is my table structure. l ran the procedure below and it works when l run it. If l use say the dates
between v_start_date being 01-11-2001 and v_end_date being 05-11-2001. l run it the first time and l get the same number of records in the two tables. When l run it the second time using the same date ranges it only populates the workflow_capital
table and no records in the history table ecauses they exist. But when l run it with dates from the 6th to the 7th
it copies duplicates into the history table.
How can l ensure that the third test scenario does not insert duplicates ?l even created a new column and called it run_no
purpose being to run the proc and populate the column with a run number so that my where clause includes the run number but it did not work? How can l fix this ????
q) the group by clause, do l have to have it on all the individual queries like l have?
CREATE TABLE MIS_WRKFLW_CAPITAL (
BRANCH NUMBER (10),
SBROKERCD VARCHAR2 (10),
RECEIVED NUMBER (15,2),
REC_COUNT NUMBER (10),
PREQUOTA NUMBER (15,2),
PREQUOTA_COUNT NUMBER (10),
BATCHED NUMBER (15,2),
BATCHED_COUNT NUMBER (10),
SCANNED_RECEIVED NUMBER (15,2),
SCANREC_COUNT NUMBER (10),
CAPTURED NUMBER (15,2),
CPA_COUNT NUMBER (10),
CHECKED NUMBER (15,2),
CHKD_COUNT NUMBER (10),
RULES NUMBER (15,2),
RULES_COUNT NUMBER (10),
RETURNED NUMBER (15,2),
RET_COUNT NUMBER (10),
DISBURSE NUMBER (15,2),
DIS_COUNT NUMBER (10),
ACTIVE NUMBER (15,2),
ACTIVE_COUNT NUMBER (10),
PRE_REJECT NUMBER (15,2),
PREREJ_COUNT NUMBER (10),
REJECTED NUMBER (15,2),
REJ_COUNT NUMBER (10),
SUMMARY_DATE DATE,
CHANGE_DATE DATE,
RUN_NO NUMBER (5))
CREATE OR REPLACE Procedure Mis_WorkFlow_Capital
(v_start_date DATE,v_end_date DATE,v_num number)
IS
v_cnt number;
v_run_no number;
BEGIN
insert into MIS_WRKFLW_Capital(branch,sbrokercd,Received,rec_count,run_no,summary_date,change_date)
select branch,sbrokercd, sum(Capital),count(Capital),v_run_no,sysdate,v_start_date
from ZW30800P
where SCDATE between v_start_date and v_end_date
group by rollup(branch,sbrokercd);
insert into MIS_WRKFLW_Capital(branch,sbrokercd,Prequota,prequota_count,run_no,summary_date,change_date)
select branch,sbrokercd,sum(Capital),count(Capital),v_run_no,sysdate,v_start_date
from ZW30800P
where STATUS = 'PRE'
and PQDATE between v_start_date and v_end_date
group by rollup(branch,sbrokercd);
insert into MIS_WRKFLW_Capital (branch,sbrokercd,Batched,batched_count,run_no,summary_date,change_date)
select branch,sbrokercd,sum(Capital),count(Capital),v_run_no,sysdate,v_start_date
from ZW30800P
where STATUS = 'BAT'
and BADATE between v_start_date and v_end_date
group by rollup(branch,sbrokercd);
insert into MIS_WRKFLW_Capital(branch,sbrokercd,scanned_received,scanrec_count,run_no,summary_date,change_date)
select branch,sbrokercd,sum(Capital),count(Capital),v_run_no,sysdate,v_start_date
from ZW30800P
where STATUS = 'SCA'
and SCDATE between v_start_date and v_end_date
group by rollup(branch,sbrokercd);
insert into MIS_WRKFLW_Capital(branch,sbrokercd,captured,cpa_count,run_no,summary_date,change_date)
select branch,sbrokercd,sum(Capital),count(Capital),v_run_no,sysdate,v_start_date
from ZW30800P
where STATUS = 'CAP'
and CAPDATE between v_start_date and v_end_date
group by rollup(branch,sbrokercd);
insert into MIS_WRKFLW_Capital(branch,sbrokercd,checked,chkd_count,run_no,summary_date,change_date)
select branch,sbrokercd,sum(Capital),count(Capital),v_run_no,sysdate,v_start_date
from ZW30800P
where STATUS = 'CHK'
and CHKDATE between v_start_date and v_end_date
group by rollup(branch,sbrokercd);
insert into MIS_WRKFLW_Capital(branch,sbrokercd,rules,rules_count,run_no,summary_date,change_date)
select branch,sbrokercd,sum(Capital),count(Capital),v_run_no,sysdate,v_start_date
from ZW30800P
where STATUS = 'RUL'
and LASTCHGDAT between v_start_date and v_end_date
group by rollup(branch,sbrokercd);
insert into MIS_WRKFLW_Capital(branch,sbrokercd,disburse,dis_count,run_no,summary_date,change_date)
select branch,sbrokercd,sum(Capital),count(Capital),v_run_no,sysdate,v_start_date
from ZW30800P
where STATUS = 'ERR'
and RETDATE between v_start_date and v_end_date
group by rollup(branch,sbrokercd);
insert into MIS_WRKFLW_Capital(branch,sbrokercd,active,active_count,run_no,summary_date,change_date)
select branch,sbrokercd,sum(Capital),count(Capital),v_run_no,sysdate,v_start_date
from ZW30800P
where STATUS = 'DIS'
and LASTCHGDAT between v_start_date and v_end_date
group by rollup(branch,sbrokercd);
insert into MIS_WRKFLW_Capital(branch,sbrokercd,pre_reject,prerej_count,run_no,summary_date,change_date)
select branch,sbrokercd,sum(Capital),count(Capital),v_run_no,sysdate,v_start_date
from ZW30800P
where STATUS = 'ACT'
and DISDATE between v_start_date and v_end_date
group by rollup(branch,sbrokercd);
insert into MIS_WRKFLW_Capital(branch,sbrokercd,rejected,rej_count,run_no,summary_date,change_date)
select branch,sbrokercd,sum(Capital),count(Capital),v_run_no,sysdate,v_start_date
from ZW30800P
where STATUS = 'REF'
and PRDATE between v_start_date and v_end_date
group by rollup(branch,sbrokercd);
insert into MIS_WRKFLW_Capital(branch,sbrokercd,rejected,rec_count,run_no,summary_date,change_date)
select branch,sbrokercd,sum(Capital),count(Capital),v_run_no,sysdate,v_start_date
from ZW30800P
where STATUS = 'REJ'
and REJDATE between v_start_date and v_end_date
group by rollup(branch,sbrokercd);
v_cnt := 0;
v_run_no := 0;
select count(*) into v_cnt
from MIS_wrkflw_Cap_hist
where to_date(v_start_date,'yyyy-mm-dd') = to_date(change_date,'yyyy-mm-dd')
and to_date(sysdate,'yyyy-mm-dd') = to_date(summary_date,'yyyy-mm-dd')
and run_no = run_no ;
if v_cnt = 0
Then
INSERT INTO MIS_wrkflw_Cap_hist(
branch ,
sbrokercd,
received,
rec_count,
prequota,
prequota_count,
batched,
batched_count,
scanned_received,
scanrec_count,
captured,
cpa_count,
checked,
chkd_count,
rules,
rules_count,
returned,
ret_count,
disburse,
dis_count,
active,
active_count,
pre_reject,
prerej_count,
rejected,
rej_count,
run_no,
summary_date,
change_date)
select branch,
sbrokercd,
received,
rec_count,
prequota,
prequota_count,
batched,
batched_count,
scanned_received,
scanrec_count,
captured,
cpa_count,
checked,
chkd_count,
rules,
rules_count,
returned,
ret_count,
disburse,
dis_count,
active,
active_count,
pre_reject,
prerej_count,
rejected,
rej_count,
run_no,
summary_date,
change_date
from mis_wrkflw_Capital;
v_run_no := v_run_no + 1;
end if;
end;
/
|
|
|
|
| Re: duplicates help !!!!!!!!!!!! [message #18653 is a reply to message #18650] |
Fri, 08 February 2002 18:50  |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
I don't know what your keys are, but if you have them you can do something like this:
--This select
--would cover all your insert statements
CURSOR m_cur
IS
SELECT
branch,
sbrokercd,
sum(Capital) capital_sum,
count(Capital) capital_count,
v_run_no,
sysdate,
v_start_date
from ZW30800P
WHERE
SCDATE between v_start_date and v_end_date
OR (SCDATE between v_start_date and v_end_date
AND STATUS IN( 'PRE', 'BAT', 'SCA','CAP', 'CHK', 'RUL',
'ERR', 'DIS', 'ACT, 'REF') )
group by rollup(branch,sbrokercd);
BEGIN
FOR r IN m_cur
LOOP
SELECT count(*) INTO v_cnt FROM MIS_WRKFLW_Capital
WHERE
--whatever keys you have:
branch=r.branch
AND sbrokercd=r.sbrokercd
AND change_date=v_start_date
....
IF v_ct<1
THEN
INSERT INTO MIS_WRKFLW_Capital ( ) VALUES (r.branch, ....)
--And you can also check history table if you need to and do your insert...
|
|
|
|
| Re: duplicates help !!!!!!!!!!!! [message #18659 is a reply to message #18650] |
Sat, 09 February 2002 10:40 |
Rm69
Messages: 39
Registered: January 2002
|
Member |
|
|
l have rewritten the script basing it on the advice you gave me.This is the error that l get when l compile my procedure
PLS-00103: Encountered the symbol ";" when expecting one of the following:
loop
q1)My objective is to insert data from table zw30800p into mis_wrkflw_Capital table and at the same time insert data into the mis_wrkflw_cap_hist table. But this procedure should check if the record exists already in the mis_wrkflw_cap_hist table if it does it should not insert it.But if it does not then it should insert it. and need guidance.
Create or Replace Procedure InsertCapitalProc
(v_start_date date,v_end_date date)
As
CURSOR m_cur
IS
SELECT
branch,
sbrokercd,
sum(Capital) capital_sum,
count(Capital) capital_count,
v_run_no,
sysdate,
v_start_date
from ZW30800P
WHERE
SCDATE between v_start_date and v_end_date
OR SCDATE between v_start_date and v_end_date
OR REJDATE between v_start_date and v_end_date
OR PRDATE between v_start_date and v_end_date
OR DISDATE between v_start_date and v_end_date
OR LASTCHGDAT between v_start_date and v_end_date
OR RETDATE between v_start_date and v_end_date
OR CHKDATE between v_start_date and v_end_date
OR CAPDATE between v_start_date and v_end_date
OR BADATE between v_start_date and v_end_date
OR PQDATE between v_start_date and v_end_date
AND STATUS IN( 'PRE', 'BAT', 'SCA','CAP', 'CHK', 'RUL',
'ERR', 'DIS', 'ACT', 'REF')
group by rollup(branch,sbrokercd);
BEGIN
FOR r IN m_cur
LOOP
SELECT count(*) INTO v_cnt FROM MIS_WRKFLW_Capital
WHERE
--keys you have:
branch=r.branch
AND sbrokercd=r.sbrokercd
AND change_date=v_start_date
AND received=r.received
AND rec_count=r.rec_count
AND r.prequota=prequota
AND r.prequota_count=prequota_count
AND r.batched = batched
AND r.batched_count =batched_count
AND r.scanned_received=scanned_received
AND r.scanrec_count=scanrec_count
AND r.captured=captured
AND r.cpa_count=cpa_count
AND r.checked=checked
AND r.chkd_count=chkd_count
AND r.rules=rules
AND r.rules_count=rules_count
AND r.returned=returned
AND r.ret_count=ret_count
AND r.disburse=disburse
AND r.dis_count=dis_count
AND r.active=active
AND r.active_count=active_count
AND r.pre_reject=pre_reject
AND r.prerej_count=prerej_count
AND r.rejected=rejected
AND r.rej_count=rej_count
AND r.run_no=run_no
AND r.summary_date=summary_date
AND r.change_date=change_date;
IF v_ct < 1
THEN
INSERT INTO MIS_WRKFLW_Capital (branch,
sbrokercd,
received,
rec_count,
prequota,
prequota_count,
batched,
batched_count,
scanned_received,
scanrec_count,
captured,
cpa_count,
checked,
chkd_count,
rules,
rules_count,
returned,
ret_count,
disburse,
dis_count,
active,
active_count,
pre_reject,
prerej_count,
rejected,
rej_count,
run_no,
summary_date,
change_date)
VALUES (r.branch,r.sbrokercd,v_start_date,r.received,r.rec_count,
r.prequota,r.prequota_count,r.batched,r.batched_count,r.scanned_received,r.scanrec_count,r.captured,
r.cpa_count,r.checked,r.chkd_count,r.rules,r.rules_count,r.returned,r.ret_count,r.disburse,r.dis_count,
r.active,r.active_count,r.pre_reject,r.prerej_count,r.rejected,r.rej_count,r.run_no,r.summary_date,
r.change_date);
--And you can also check history table if you need to and do your insert...
IF v_ct = 0
THEN
INSERT INTO MIS_WRKFLW_Cap_Hist (branch,
sbrokercd,
received,
rec_count,
prequota,
prequota_count,
batched,
batched_count,
scanned_received,
scanrec_count,
captured,
cpa_count,
checked,
chkd_count,
rules,
rules_count,
returned,
ret_count,
disburse,
dis_count,
active,
active_count,
pre_reject,
prerej_count,
rejected,
rej_count,
run_no,
summary_date,
change_date)
VALUES (r.branch,r.sbrokercd,v_start_date,r.received,r.rec_count,
r.prequota,r.prequota_count,r.batched,r.batched_count,r.scanned_received,r.scanrec_count,r.captured,
r.cpa_count,r.checked,r.chkd_count,r.rules,r.rules_count,r.returned,r.ret_count,r.disburse,r.dis_count,
r.active,r.active_count,r.pre_reject,r.prerej_count,r.rejected,r.rej_count,r.run_no,r.summary_date,
r.change_date);
--close m_cur;
end if;
end if;
end;
q2) l have created a new column called run_no so that each time l run the procedure l insert the run_no and it increaments each time it runs.This is my table structure
CREATE TABLE MIS_WRKFLW_CAPITAL (
BRANCH NUMBER (10),
SBROKERCD VARCHAR2 (10),
RECEIVED NUMBER (15,2),
REC_COUNT NUMBER (10),
PREQUOTA NUMBER (15,2),
PREQUOTA_COUNT NUMBER (10),
BATCHED NUMBER (15,2),
BATCHED_COUNT NUMBER (10),
SCANNED_RECEIVED NUMBER (15,2),
SCANREC_COUNT NUMBER (10),
CAPTURED NUMBER (15,2),
CPA_COUNT NUMBER (10),
CHECKED NUMBER (15,2),
CHKD_COUNT NUMBER (10),
RULES NUMBER (15,2),
RULES_COUNT NUMBER (10),
RETURNED NUMBER (15,2),
RET_COUNT NUMBER (10),
DISBURSE NUMBER (15,2),
DIS_COUNT NUMBER (10),
ACTIVE NUMBER (15,2),
ACTIVE_COUNT NUMBER (10),
PRE_REJECT NUMBER (15,2),
PREREJ_COUNT NUMBER (10),
REJECTED NUMBER (15,2),
REJ_COUNT NUMBER (10),
SUMMARY_DATE DATE,
CHANGE_DATE DATE,
RUN_NO NUMBER (5))
|
|
|
|
| Re: duplicates help !!!!!!!!!!!! [message #18664 is a reply to message #18659] |
Sun, 10 February 2002 04:52 |
Rm69
Messages: 39
Registered: January 2002
|
Member |
|
|
error:- PLS-00302: component 'RECEIVED' must be declared.
Mis_wrkflw_capital can have duplicates only the history table should not contain dups.If l declare all the components my procedure retains nothing.l'm getting lost
Create or Replace Procedure InsertCapitalProcE
(v_start_date date,v_end_date date)
As
CURSOR m_cur
IS
SELECT
branch,
sbrokercd,
sum(Capital) capital_sum,
count(Capital) capital_count,
--v_run_no,
sysdate,
v_start_date
from ZW30800P
WHERE
SCDATE between v_start_date and v_end_date
OR SCDATE between v_start_date and v_end_date
OR REJDATE between v_start_date and v_end_date
OR PRDATE between v_start_date and v_end_date
OR DISDATE between v_start_date and v_end_date
OR LASTCHGDAT between v_start_date and v_end_date
OR RETDATE between v_start_date and v_end_date
OR CHKDATE between v_start_date and v_end_date
OR CAPDATE between v_start_date and v_end_date
OR BADATE between v_start_date and v_end_date
OR PQDATE between v_start_date and v_end_date
AND STATUS IN( 'PRE', 'BAT', 'SCA','CAP', 'CHK', 'RUL',
'ERR', 'DIS', 'ACT', 'REF')
group by rollup(branch,sbrokercd);
BEGIN
FOR r IN m_cur
LOOP
SELECT count(*) INTO v_cnt FROM MIS_WRKFLW_Capital
WHERE
branch=r.branch
AND sbrokercd=r.sbrokercd
AND change_date=v_start_date
AND received=r.received
AND rec_count=r.rec_count
AND r.prequota=prequota
AND r.prequota_count=prequota_count
AND r.batched = batched
AND r.batched_count =batched_count
AND r.scanned_received=scanned_received
AND r.scanrec_count=scanrec_count
AND r.captured=captured
AND r.cpa_count=cpa_count
AND r.checked=checked
AND r.chkd_count=chkd_count
AND r.rules=rules
AND r.rules_count=rules_count
AND r.returned=returned
AND r.ret_count=ret_count
AND r.disburse=disburse
AND r.dis_count=dis_count
AND r.active=active
AND r.active_count=active_count
AND r.pre_reject=pre_reject
AND r.prerej_count=prerej_count
AND r.rejected=rejected
AND r.rej_count=rej_count
AND r.run_no=run_no
AND r.summary_date=summary_date
AND r.change_date=change_date;
IF v_ct < 1 --If no records match..
THEN
INSERT INTO MIS_WRKFLW_Capital (branch,
sbrokercd,
received,
rec_count,
prequota,
prequota_count,
batched,
batched_count,
scanned_received,
scanrec_count,
captured,
cpa_count,
checked,
chkd_count,
rules,
rules_count,
returned,
ret_count,
disburse,
dis_count,
active,
active_count,
pre_reject,
prerej_count,
rejected,
rej_count,
run_no,
summary_date,
change_date)
VALUES (r.branch,r.sbrokercd,v_start_date,r.received,r.rec_count,
r.prequota,r.prequota_count,r.batched,r.batched_count,r.scanned_received,r.scanrec_count,r.captured,
r.cpa_count,r.checked,r.chkd_count,r.rules,r.rules_count,r.returned,r.ret_count,r.disburse,r.dis_count,
r.active,r.active_count,r.pre_reject,r.prerej_count,r.rejected,r.rej_count,r.run_no,r.summary_date,
r.change_date);
--IF v_ct = 0 --You do not need this IF,
--you already checked on this condition.
--THEN
INSERT INTO MIS_WRKFLW_Cap_Hist (branch,
sbrokercd,
received,
rec_count,
prequota,
prequota_count,
batched,
batched_count,
scanned_received,
scanrec_count,
captured,
cpa_count,
checked,
chkd_count,
rules,
rules_count,
returned,
ret_count,
disburse,
dis_count,
active,
active_count,
pre_reject,
prerej_count,
rejected,
rej_count,
run_no,
summary_date,
change_date)
VALUES (r.branch,r.sbrokercd,v_start_date,r.received,r.rec_count,
r.prequota,r.prequota_count,r.batched,r.batched_count,r.scanned_received,r.scanrec_count,r.captured,
r.cpa_count,r.checked,r.chkd_count,r.rules,r.rules_count,r.returned,r.ret_count,r.disburse,r.dis_count,
r.active,r.active_count,r.pre_reject,r.prerej_count,r.rejected,r.rej_count,r.run_no,r.summary_date,
r.change_date);
--close m_cur;
END IF;
--end if; --
end loop;
END;
|
|
|
|
| Re: duplicates help !!!!!!!!!!!! [message #18666 is a reply to message #18664] |
Sun, 10 February 2002 14:01 |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
In this case put your IF afrer you populate your table(this way you get all the records you want) and before you start populating HIST table. This way you get rid of dupes. And check yout syntax and column names.
Create or Replace Procedure InsertCapitalProcE
(v_start_date date,v_end_date date)
As
CURSOR m_cur
IS
SELECT
branch,
sbrokercd,
sum(Capital) capital_sum,
count(Capital) capital_count,
--v_run_no,
sysdate,
v_start_date
from ZW30800P
WHERE
SCDATE between v_start_date and v_end_date
OR SCDATE between v_start_date and v_end_date
OR REJDATE between v_start_date and v_end_date
OR PRDATE between v_start_date and v_end_date
OR DISDATE between v_start_date and v_end_date
OR LASTCHGDAT between v_start_date and v_end_date
OR RETDATE between v_start_date and v_end_date
OR CHKDATE between v_start_date and v_end_date
OR CAPDATE between v_start_date and v_end_date
OR BADATE between v_start_date and v_end_date
OR PQDATE between v_start_date and v_end_date
AND STATUS IN( 'PRE', 'BAT', 'SCA','CAP', 'CHK', 'RUL',
'ERR', 'DIS', 'ACT', 'REF')
group by rollup(branch,sbrokercd);
BEGIN
FOR r IN m_cur
LOOP
SELECT count(*) INTO v_cnt FROM MIS_WRKFLW_Capital
WHERE
branch=r.branch
AND sbrokercd=r.sbrokercd
AND change_date=v_start_date
AND received=r.received
AND rec_count=r.rec_count
AND r.prequota=prequota
AND r.prequota_count=prequota_count
AND r.batched = batched
AND r.batched_count =batched_count
AND r.scanned_received=scanned_received
AND r.scanrec_count=scanrec_count
AND r.captured=captured
AND r.cpa_count=cpa_count
AND r.checked=checked
AND r.chkd_count=chkd_count
AND r.rules=rules
AND r.rules_count=rules_count
AND r.returned=returned
AND r.ret_count=ret_count
AND r.disburse=disburse
AND r.dis_count=dis_count
AND r.active=active
AND r.active_count=active_count
AND r.pre_reject=pre_reject
AND r.prerej_count=prerej_count
AND r.rejected=rejected
AND r.rej_count=rej_count
AND r.run_no=run_no
AND r.summary_date=summary_date
AND r.change_date=change_date;
--If you need all record including dupes here do
--do not do thid IF
--IF v_ct < 1 --If no records match..
--THEN
INSERT INTO MIS_WRKFLW_Capital (branch,
sbrokercd,
received,
rec_count,
prequota,
prequota_count,
batched,
batched_count,
scanned_received,
scanrec_count,
captured,
cpa_count,
checked,
chkd_count,
rules,
rules_count,
returned,
ret_count,
disburse,
dis_count,
active,
active_count,
pre_reject,
prerej_count,
rejected,
rej_count,
run_no,
summary_date,
change_date)
VALUES (r.branch,r.sbrokercd,v_start_date,r.received,r.rec_count,
r.prequota,r.prequota_count,r.batched,r.batched_count,r.scanned_received,r.scanrec_count,r.captured,
r.cpa_count,r.checked,r.chkd_count,r.rules,r.rules_count,r.returned,r.ret_count,r.disburse,r.dis_count,
r.active,r.active_count,r.pre_reject,r.prerej_count,r.rejected,r.rej_count,r.run_no,r.summary_date,
r.change_date);
--Here you check for dupes
IF v_ct < 1 --If no records match..
THEN
INSERT INTO MIS_WRKFLW_Cap_Hist (branch,
sbrokercd,
received,
rec_count,
prequota,
prequota_count,
batched,
batched_count,
scanned_received,
scanrec_count,
captured,
cpa_count,
checked,
chkd_count,
rules,
rules_count,
returned,
ret_count,
disburse,
dis_count,
active,
active_count,
pre_reject,
prerej_count,
rejected,
rej_count,
run_no,
summary_date,
change_date)
VALUES (r.branch,r.sbrokercd,v_start_date,r.received,r.rec_count,
r.prequota,r.prequota_count,r.batched,r.batched_count,r.scanned_received,r.scanrec_count,r.captured,
r.cpa_count,r.checked,r.chkd_count,r.rules,r.rules_count,r.returned,r.ret_count,r.disburse,r.dis_count,
r.active,r.active_count,r.pre_reject,r.prerej_count,r.rejected,r.rej_count,r.run_no,r.summary_date,
r.change_date);
--close m_cur;
END IF;
--end if; --
end loop;
END;
|
|
|
|
| Re: duplicates help !!!!!!!!!!!! [message #18668 is a reply to message #18650] |
Sun, 10 February 2002 18:30 |
Rm69
Messages: 39
Registered: January 2002
|
Member |
|
|
This is table zw30800p. Used the original inserts and got it working, but for interest's sake l would like to write the cursor in the way you did it using these selects cause it makes more sense and is readable too.Otherwise if you you think you've spent more than enough time om my questions l'll understand.Ta mate
CREATE TABLE ZW30800P (
STATUS VARCHAR2 (3),
TYPE VARCHAR2 (1),
DEALREF VARCHAR2 (15),
COMPANY VARCHAR2 (3),
BRANCH NUMBER (5),
PRODUCT VARCHAR2 (5),
BROKERCD VARCHAR2 (10),
SBROKERCD VARCHAR2 (10),
LOANOFF VARCHAR2 (10),
CAPITAL NUMBER (15,2),
PDINCL NUMBER (15,2),
PDEXCL NUMBER (15,2),
PQDATE DATE,
PQTIME NUMBER (6),
BADATE DATE,
SCDATE DATE,
SCTIME NUMBER (6),
CAPDATE DATE,
REJWAYB VARCHAR2 (20),
REJBTCH NUMBER (9),
REJUSER VARCHAR2 (10),
REJDATE DATE,
REJTIM NUMBER (6),
CHKUSER VARCHAR2 (10),
CHKDATE DATE,
CHKTIME NUMBER (6),
RETUSER VARCHAR2 (10),
RETDATE DATE,
RETTIME NUMBER (6),
DISUSER VARCHAR2 (10),
DISDATE DATE,
DISTIME NUMBER (6),
KNOWNAME VARCHAR2 (50),
EFTTYPE VARCHAR2 (2),
ISDAY NUMBER (2),
PRDATE DATE,
RCDATE DATE,
LASTCHGDAT DATE,
|
|
|
|
Goto Forum:
Current Time: Sun May 18 22:08:02 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
