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: Borislav Borissov
  Where is Borislav Borissov?
 Sofia
 Bulgaria
 Borislav Borissov
 Tags
Subject: RE: SQL Statement-FoxPro doesn't like it!
Thread ID: 395523 Message ID: 395534 # Views: 46 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Wednesday, January 15, 2014 6:19:03 PM         
   


> > FoxPro doesn't like something in the below statement. I added a condition to get the MAX Effective Date. The error I see is "Column 'GRPID' is not found." Oh, I think the substatement needs to also have GrpID and GrpNum.
> >
> >
> > SELECT DISTINCT cv.grpid, cv.grpnum, cv.bnf, cv.planKey, cv.Status ;
> > 	FROM grpextcv cv ;
> > 	JOIN (select MAX(Eff_Date) AS Eff_Date FROM GRPEXTCV GROUP BY Eff_Date) 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.
>
>
> This is wrong:
>
> JOIN (select MAX(Eff_Date) AS Eff_Date FROM GRPEXTCV GROUP BY Eff_Date) cv2 ;
> 		ON cv.GrpID+cv.GrpNum=cv2.GrpID+cv2.GrpNum ;
> 

>
> You select ONLY Eff_Date, but join the result by GrpID and GrpNum
>
> -----------------
> Borislav Borissov
>
> Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
> The only thing normal about database guys is their tables.

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.

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