Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Jun Tangunan
  Where is Jun Tangunan?
 Cabanatuan
 Philippines
 Jun Tangunan
 To: Tore Bleken
  Where is Tore Bleken?
 Stokke
 Norway
 Tore Bleken
 Tags
Subject: RE: Over-Killed Dupes in SQL Statement
Thread ID: 396048 Message ID: 396142 # Views: 45 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Thursday, January 23, 2014 3:18:33 AM         
   


> > I am seeing that in my effort to kills just a few dupes, about 10 or more, I killed off half the records that were supposed to get through, from over 3600 down to 1800+. So, my SQL JOIN statement somehow over-did it: JOIN (select GrpID, GrpNum, MAX(Eff_Date) AS Eff_Date FROM GRPEXTCV GROUP BY GrpID, GrpNum) cv2 ON cv.GrpID+cv.GrpNum=cv2.GrpID+cv2.GrpNum.
> >
> > How can I rewrite this so as not to over-do-it?
> >
> >
> > SELECT DISTINCT cv.grpid, cv.grpnum, cv.bnf, cv.planKey, cv.Status ;
> > 	FROM grpextcv cv ;
> > 	JOIN (select GrpID, GrpNum, MAX(Eff_Date) AS Eff_Date FROM GRPEXTCV GROUP BY GrpID, GrpNum) cv2 ;
> > 		ON cv.GrpID+cv.GrpNum=cv2.GrpID+cv2.GrpNum ;
> > 	WHERE ;
> > 		cv.Hist="99" AND ;
> > 		cv.GrpID="APA" AND ;
> > 		cv.PlanKey="DB1" AND ;
> > 		(INLIST(cv.Status, "A", "B") OR ;
> > 			(cv.Status="T" AND BETWEEN(cv.CHG_DATE, ldBegin, ldEnd))) ;
> > 	ORDER BY cv.grpid, cv.grpnum ;
> > 	INTO cursor tmpData2
> > 

> >
> > I think that the following is one way to do this; it may not be the best or most eloquent way to do this, but it seems to work.
> >
> >
> > SELECT GrpID, GrpNum, MAX(Eff_Date) AS Eff_Date FROM GRPEXTCV cv GROUP BY GrpID, GrpNum ;
> > 	WHERE cv.Hist="99" AND ;
> > 		cv.GrpID="APA" AND ;
> > 		cv.PlanKey="DB1" AND ;
> > 		(INLIST(cv.Status, "A", "B") OR ;
> > 			(cv.Status="T" AND BETWEEN(cv.CHG_DATE, ldBegin, ldEnd))) ;
> > 	ORDER BY cv.grpid, cv.grpnum ;
> > 	INTO cursor tmpData
> > 
> > SELECT DISTINCT cv.grpid, cv.grpnum, cv.bnf, cv.planKey, cv.Status ;
> > 	FROM grpextcv cv ;
> > 	JOIN tmpData td ON (cv.GrpID+cv.GrpNum=td.GrpID+td.GrpNum) AND ;
> > 			cv.Eff_Date=td.Eff_Date ;
> > 	WHERE ;
> > 		cv.Hist="99" AND ;
> > 		cv.GrpID="APA" AND ;
> > 		cv.PlanKey="DB1" AND ;
> > 		(INLIST(cv.Status, "A", "B") OR ;
> > 			(cv.Status="T" AND BETWEEN(cv.CHG_DATE, ldBegin, ldEnd))) ;
> > 	ORDER BY cv.grpid, cv.grpnum ;
> > 	INTO cursor tmpData2
> > 

> >
> > Cecil Champenois, Jr.
>
> You must add Readwrite or Nofilter to the first Select statement. If you don't, the Where clause in the first Select statement will be ignored when you run the second Select.
>
> There's a fine line between helping and adding to the confusion.
>
> How to create sample data


AFAIK, the first SQL SELECT will automatically create a true cursor even without those clauses due to MAX().

The second SQL SELECT as well due to DISTINCT.

http://sandstorm36.blogspot.com.au/2011/09/nofilter-clause-on-sql-select-when-to.html




https://vfpx.codeplex.com/releases/view/99045
http://sandstorm36.blogspot.com

ENTIRE THREAD

Over-Killed Dupes in SQL Statement Posted by Cecil Champenois @ 1/22/2014 2:29:01 PM
RE: Over-Killed Dupes in SQL Statement Posted by Tore Bleken @ 1/22/2014 3:04:05 PM
RE: Over-Killed Dupes in SQL Statement Posted by Jun Tangunan @ 1/23/2014 3:18:33 AM
RE: Over-Killed Dupes in SQL Statement Posted by Jun Tangunan @ 1/23/2014 3:18:39 AM
RE: Over-Killed Dupes in SQL Statement Posted by Cecil Champenois @ 1/23/2014 12:59:22 PM
RE: Over-Killed Dupes in SQL Statement Posted by Ali Koumaiha @ 1/23/2014 4:07:24 PM
RE: Over-Killed Dupes in SQL Statement Posted by Pete Sass @ 1/23/2014 4:07:46 PM
RE: Over-Killed Dupes in SQL Statement Posted by Cecil Champenois @ 1/23/2014 5:28:11 PM
RE: Over-Killed Dupes in SQL Statement Posted by Gene Wirchenko @ 1/23/2014 7:58:52 PM
RE: Over-Killed Dupes in SQL Statement Posted by Jun Tangunan @ 1/24/2014 12:05:00 AM
RE: Over-Killed Dupes in SQL Statement Posted by Anders Altberg @ 1/25/2014 1:31:47 AM
RE: Over-Killed Dupes in SQL Statement Posted by Christian Tabligan @ 1/25/2014 2:32:49 PM