Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Eliminating dups using multiple tables
Message
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Versions des environnements
Visual FoxPro:
VFP 9 SP1
OS:
Windows XP
Database:
Visual FoxPro
Divers
Thread ID:
01128331
Message ID:
01128354
Vues:
25
You could do this in one select (I don't say it will be fasterm but you could try)
select cPatients_pk, tEntered_Date, tLast_Updated_Date, Patients.dDOB, ;
	cSSN, cSex_Code, cntDups,;
        Names.cf_Name, Names.cl_Name, Names.cM_Initial, ;
        Address.cStreet1, Address.cCity, ;
        nvl(cAREA_CODE,space(3)) as cArea_Code, ;
        nvl(cEXCHANGE,space(3)) as cExchange, ;
        nvl(cLAST_FOUR, space(4)) as cLast_Four,;
FROM (SELECT COUNT(*) AS cntDupsm, MAX(cPatients_pk) AS cPatients_pk, MAX(tEntered_Date) AS tEntered_Date,
             MAX(tLast_Updated_Date) AS tLast_Updated_Date, dDOB, cSSN, MAX(cSex_Code) AS cSex_Code;
      FROM Patients;
      GROUP BY cSSN,dDOB;
      WHERE NOT EMPTY(cSSn) AND NOT EMPTY(dDOB);
      HAVING cntDupsm > 1) cr;
inner join Names   on cr.cPatients_pk = Names.cPointer_fk ;
inner join Address on cr.cPatients_pk = Address.cPointer_fk ;
left  join Phones  on cr.cPatients_pk = Phones.cPointer_fk ;
order by dDOB, cSSN, cSEX_Code,Names.cl_Name, Names.cf_Name;
into cursor curDupPatients readwrite
(not tested)


>This seems to be much better:
>
>
>select cPatients_pk, tEntered_Date, ;
>	tLast_Updated_Date, Patients.dDOB, ;
>	Patients.cSSN, Patients.cSex_Code, step1.cntDups from Patients ;
>	inner join ;
>	(select count(*) as cntDups, Patients.dDOB, Patients.cSSN ;
>	from Patients group by 2,3 having cntDups >1 where cSSN<>space(9) and dDOB<>{}) step1 ;
>	on Patients.dDOB = step1.dDOB and Patients.cSSN = step1.cSSN ;
>	order by Patients.dDOB, Patients.cSSN ;
>	into cursor curPossibleDups
>	index on cPatients_pk tag cPat_pk	
>	set order to
>	
>	select  cr.cPatients_pk, cr.tEntered_Date, ;
>			cr.tLast_Updated_Date, cr.dDOB, cr.cSSN, cr.cSex_Code, ;			
>			Names.cf_Name, Names.cl_Name, Names.cM_Initial, ;
>			Address.cStreet1, Address.cCity, ;
>			nvl(cAREA_CODE,space(3)) as cArea_Code, ;
>			nvl(cEXCHANGE,space(3)) as cExchange, ;
>			nvl(cLAST_FOUR, space(4)) as cLast_Four, cntDups ;
>			from curPossibleDups cr inner join Names ;
>			on cr.cPatients_pk = Names.cPointer_fk ;
>			inner join Address ;
>			on cr.cPatients_pk = Address.cPointer_fk ;
>			left join Phones ;
>			on cr.cPatients_pk = Phones.cPointer_fk ;
>			order by 4,5,6,8,7 ;
>			into cursor curDupPatients readwrite
>
>Now I can see multiple UNKNOWN patients and also some person with date of birth 08/26/1864 <g>
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform