Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cecil Champenois
  Where is Cecil Champenois?
 Little Elm
 Texas - United States
 Cecil Champenois
 To: Koen Piller
  Where is Koen Piller?
 Santpoort-Zuid
 Netherlands
 Koen Piller
 Tags
Subject: RE: SQL Statement-FoxPro doesn't like it!
Thread ID: 395523 Message ID: 395539 # Views: 41 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Wednesday, January 15, 2014 6:50:02 PM         
   


> >
> > Yes, you are correct. I saw, after I had posted the above, that I had left out fields which needed to be there in the secondary select statement.
> > Here's the revised code:
> >
> > * Pull the data...
> > * 01/15/2014 Cecil Champenois. Added code to also ensure that if there is more
> > * than one record for the same insured, that we get the record having the latest
> > * effective date. Added the JOIN statement and the first WHERE statement.
> > 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.Eff_Date=cv2.Eff_Date AND ;
> > 		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
> > 

> >
> > Cecil Champenois, Jr.
>
>
> Cecil,
>
> what is the result of your query? what did you expect?
> Any change to give us sample date to reproduce?
> Rgds,
> Koen

After correcting the code, the query worked fine. The code does work properly now.

I can probably give a sample. I'll have to modify some of the data though, since it's private:

GrpID GrpNum Benefit PlanKey Status
APA 0001234 0001400 DB1 B
APA 0002345 0004800 DB1 B
APA 0003456 0004800 DB1 A
APA 0004567 0006000 DB1 B


Cecil Champenois, Jr.

ENTIRE THREAD

SQL Statement-FoxPro doesn't like it! Posted by Cecil Champenois @ 1/15/2014 6:04:44 PM
RE: SQL Statement-FoxPro doesn't like it! Posted by Borislav Borissov @ 1/15/2014 6:16:25 PM
RE: SQL Statement-FoxPro doesn't like it! Posted by Cecil Champenois @ 1/15/2014 6:19:03 PM
RE: SQL Statement-FoxPro doesn't like it! Posted by Koen Piller @ 1/15/2014 6:32:50 PM
RE: SQL Statement-FoxPro doesn't like it! Posted by Cecil Champenois @ 1/15/2014 6:50:02 PM