Home » SQL & PL/SQL » SQL & PL/SQL » Deleting duplicate records from oracle object
Deleting duplicate records from oracle object [message #227527] Wed, 28 March 2007 08:33 Go to next message
yraghavendra
Messages: 27
Registered: August 2005
Location: India
Junior Member
Hi,
Can you tell me how do I delete the duplicate records from the oracle object type in the procedure attached.

In the procedure Iam getting few cases based on the business rules which Iam populating in the oracle object "l_caseseq_nt1" which is an collection of number type.

For these cases we can have same "drug as reported" field.
So Iam fetching all the drug as reported and populating "drugs as reported" in the object "l_as_reported_nt"

Now I want to remove the duplicate drug as reportedd from the object "l_as_reported_nt"
(I could not think of any way to populate only the distinct drug as reported in the object.)

Can you please provide your views and suggestions.

Regards,
raghavendra



Re: Deleting duplicate records from oracle object [message #227533 is a reply to message #227527] Wed, 28 March 2007 08:52 Go to previous message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
For those who don't want to open attachements:


CREATE OR REPLACE procedure temp_annual
as

s1 sys_refcursor;
s2 sys_refcursor;

str1 varchar2(4000);
str2 varchar2(4000);
str3 varchar2(4000);
str4 varchar2(4000);
str5 varchar2(4000);
str6 varchar2(4000);
str7 varchar2(4000);
     
type t1 is table of number(15);
type t2 is table of varchar2(70);

l_caseseq_nt1 t1 := t1();
l_caseseq_nt2 t1 := t1();
l_caseseq_nt3 t1 := t1();
l_as_reported_nt t2 := t2();

l_versionno case_version.version_number%type;
l_caseid case_version.case_id%type;
l_caseseq case_version.case_seq%TYPE;
l_maxverno case_version.version_number%TYPE;
l_idx_counter1 PLS_INTEGER   := 0;
l_asreported case_drug.as_reported%type;


BEGIN

str1:= 
'select cv.case_seq 
from case_version cv,adverse_event ae
where study_code = '||''''||'SA-NXY-0007'||''''||
'and cv.case_seq = ae.case_seq
and ae.serious_yn_code = '||''''||'Y'||'''' ;


str2:= 
'select rd.case_seq
from report_detail rd,report_master rm
where rd.report_master_id = rm.report_master_id
and rm.cntry_drug_apprval_numb_id in (SELECT cntry_drug_apprval_numb_id
								  	  FROM cntry_drug_approval_numb, drug_approval_number                                                                                                                                                             WHERE                                                                                                                                                                                                                                    cntry_drug_approval_numb.drug_approval_number_id = drug_approval_number.drug_approval_number_id
									  AND drug_approval_number.drug_approval_number in ('||''''||'61,021'||''''||','||''''||'14-763'||''''||')
									  )
and rm.report_type_code In ('||''''||'15 DAY IND'||''''||','||''''||'7 DAY IND'||''''||')' ;


str3:= '('||str1 ||' UNION '|| str2||')';


str4:= 

'select cv.case_seq 
from case_version cv
where 
      cv.ds_initial_received_date BETWEEN TO_DATE('
         || ''''
         || '01-JAN-2004'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'') AND TO_DATE('
         || ''''
         || '04-JAN-2007'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'')

OR cv.d_case_birth_date BETWEEN TO_DATE('
         || ''''
         || '01-JAN-2004'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'') AND TO_DATE('
         || ''''
         || '04-JAN-2007'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'')

OR cv.report_clock_start_date BETWEEN TO_DATE('
         || ''''
         || '01-JAN-2004'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'') AND TO_DATE('
         || ''''
         || '04-JAN-2007'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'')

OR cv.periodic_rep_clock_start_date BETWEEN TO_DATE('
         || ''''
         || '01-JAN-2004'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'') AND TO_DATE('
         || ''''
         || '04-JAN-2007'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'')' ;


str5:= 
'select rd.case_seq
from report_detail rd,report_master rm
where rd.report_master_id = rm.report_master_id
and rm.report_type_code In ('||''''||'15 DAY IND'||''''||','||''''||'7 DAY IND'||''''||')
and rm.submitted_date BETWEEN TO_DATE('
         || ''''
         || '01-JAN-2004'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'') AND TO_DATE('
         || ''''
         || '04-JAN-2007'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'')' ;



str6:= '('||str4 ||' UNION '|| str5||')';


str7 := 'select case_seq from ( '||str3||' intersect'||str6||')';


open s1 for str7;
fetch s1 bulk collect into l_caseseq_nt1;
close s1;


for i in 1..l_caseseq_nt1.count
loop

select version_number,case_id,case_seq into l_versionno,l_caseid,l_caseseq
from case_version 
where case_seq = l_caseseq_nt1(i) ;

select max(version_number) into l_maxverno
from case_version
where case_id = l_caseid
and status_code = 'CLD';

IF l_versionno = l_maxverno
THEN


open s1 for select upper(as_reported) 
from case_drug
where drug_type_code in ('S','I') 
and (priority_order_no = 1 OR key_ingredient_id = 276 )
and case_seq = l_caseseq ;

fetch s1 INTO l_asreported;

close s1;

l_idx_counter1 := l_idx_counter1 + 1;
l_as_reported_nt.EXTEND;
l_as_reported_nt(l_idx_counter1) := l_asreported ;

END IF;
END LOOP;

END;
/

Previous Topic: Using UTL_FILE
Next Topic: How to get either of two records?
Goto Forum:
  


Current Time: Tue Dec 06 15:58:42 CST 2016

Total time taken to generate the page: 0.08965 seconds