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

# 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
``````
0
anderdw2
• 3
1 Solution

Software 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

Author Commented:
I thought that was allowed when enclosed in [ ]
0

Software 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

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

Bye, Olaf.
0

## Featured Post

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