• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 608
  • Last Modified:

How to do simple calculation with FOXPRO SQL select statement

I'm writing a short foxpro .prg file in VFP 7 that I need to do some simple calculations.  I keep getting a syntax error and I assume it's in my function.  Two questions:

Is there a better way in VFP to see the actual errors?

More import, how can I do this simple calculation?

 
Close Data
SET EXCL OFF
SET DECIMALS TO 2

SELECT  r.batch As [Batch], r.code as [Code], r.rtndate as [Return Date], r.describe as [Description], r.farm_id as [State], ;		
		r.dm as [DM], r.hygro as [Hygro], r.cp as [CP], r.adfpro as [ADFCP], r.ndfpro as [NDFCP], r.solpro as [SPDM], ;
		CALCSPCP(r.solpro,r.cp) as [SP%CP], r.adf as [ADF], r.ndf as [NDF], r.cfiber as [peNDF], r.crufiber as [Crude Fiber], r.lignin as [Lignin], ; 
	 	CALCLNDF(r.lignin, r.ndf) as [Lignin%NDF], r.ndf30 as NDF30, r.butyrid as NDF24, r.npn as NDF12, ;
	 	r.salt as [CSPS], r.fat as [Fat], r.starch as [Starch], r.sugar as [Sugar], r.nsc as [NDC], ;
	 	r.ash as Ash, r.ca as Ca, r.p as P, r.mg as Mg, r.k as K, r.na as Na, r.cl as Cl, r.s as S, r.fe as Fe, ;	 	
	 	r.mn as Mn, r.zn as Zn, r.cu as Cu, r.ndash as Mo, r.se as Se, r.ph as pH, r.ammonia as Ammonia, ;
	 	CALCNH(r.ammonia, r.cp) as [NH3%CP], r.tit_acid as [Titratable], r.lactic as [Lactic Acid], r.acetic as [Acetic Acid], ;
	 	r.proprionic as [Proprionic Acid], r.ethanol as [1,2 Propandiol], r.yeast as [Yeast CFU/GM], r.mold as [Mold CFU/GM], ;
	 	r.don as [DON], r.tit_acid as [3AcetylDON], r.mold_qa as [15AcetyDON], r.ps1 as [PS1], r.ps2 as [PS2], r.ps3 as [PS3], ;
	 	n.gh as [NIR-GH], n.ct as [NIR-Fatty Acids], n.ff as [NIR-Soluble Fiber], n.lf as [NIR-Indf], n.eq as [Equation], n.mn as [NIR UNIT];
FROM results r;  
INNER JOIN nirdata n on n.sampleid = r.sampleid;
WHERE r.batch > 9000 AND  r.feed_type="H" AND r.nir=0;
GROUP BY r.batch, r.code
  
copy to c:\mydbfs\cs_chem1 type xls
     
RETURN

FUNCTION CALCSPCP
LPARAMETERS nSolPro,nCP
       RETURN ((nSolPro/nCP)*100) 
ENDFUNC

FUNCTION CALCLNDF
LPARAMETERS nLig,nNDF
       RETURN ((nLig/nNDF)*100) 
ENDFUNC

FUNCTION CALCNH
LPARAMETERS nAmmon,nCP
       RETURN ((nAmmon/nCP)*100) 
ENDFUNC

Open in new window

0
anderdw2
Asked:
anderdw2
  • 3
1 Solution
 
Olaf DoschkeSoftware DeveloperCommented:
names need to start with letter or _ and not contain spaces and commas or other sepcial characters, you can't do something like
SELECT ... AS [1,2 Propandiol]

Bye, Olaf.
0
 
anderdw2Author Commented:
I thought that was allowed when enclosed in [ ]
0
 
Olaf DoschkeSoftware DeveloperCommented:
It's allowed in SQL Server and I also think in MS Access, but not in foxpro. In Foxpro [] are a third pair of string delimiters like " and ' and not used to delimit field names. Therefor of course many things won't wokr, especially spaces and comma.

Bye, Olaf.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Besides: You can do calculations directly in the sql, you don't need to define functions.

Bye, Olaf.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now