Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Borislav Borissov
  Where is Borislav Borissov?
 Sofia
 Bulgaria
 Borislav Borissov
 To: Cecil Champenois
  Where is Cecil Champenois?
 Little Elm
 Texas - United States
 Cecil Champenois
 Tags
Subject: RE: SQL Statement-FoxPro doesn't like it!
Thread ID: 395523 Message ID: 395533 # Views: 61 # Ratings: 2
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Wednesday, January 15, 2014 6:16:25 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.

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