Hi,

I have a SQL select statement that is giving inconsistent results for columns that do addition and/or subtraction within a sum(). (I labeled theses columns with an "AS" name beginning with "Error" 1-4 to make them easy to find). The odd thing is that if I remove a column the proceeds one of the error columns it gives a correct result.

By my count I have not exceeded the 8192 character or 255 field limitations.

Do you have any suggestions on what the probelm might be and how to fix it?

I have attached the select statement below. I run vfp7 on win2k. I have the same problem on vfp6. I believe I have the latest service packs installed on everything.

I'll be happy to send you tables if you like.

TIA Chris Voeller

SELECT ;
INCF1.IENTID AS RETCENTNO, ;
INCF1.iYr AS YLSTIYR, ;
SUM(INCF1.bElOpRev) AS BELOPREV, ;
SUM(INCF1.bElOpExp) AS BELOPEXP, ;
SUM(INCF1.bElMnExp) AS BELMNEXP, ;
SUM(INCF1.bElDepExp) AS BELDEPEXP, ;
SUM(INCF1.bElAmUtPl) AS BELAMUTPL, ;
SUM(INCF1.bElAmUPAdj) AS BELAMUPADJ, ;
SUM(INCF1.bElAmProp) AS BELAMPROP, ;
SUM(INCF1.bElAmConv) AS BELAMCONV, ;
SUM(INCF1.bElRegDebt) AS BELREGDEBT, ;
SUM(INCF1.bElRegCred) AS BELREGCRED, ;
SUM(INCF1.bElTaxes) AS BELTAXES, ;
SUM(INCF1.bElIncTaxF) AS BELINCTAXF, ;
SUM(INCF1.bElIncTaxO) AS BELINCTAXO, ;
SUM(INCF1.bElDefIncT) AS BELDEFINCT, ;
SUM(INCF1.bElLDefInc) AS BELLDEFINC, ;
SUM(INCF1.bElInvTxCr) AS BELINVTXCR, ;
SUM(INCF1.bElGnUtPl) AS BELGNUTPL, ;
SUM(INCF1.bElLsUtPl) AS BELLSUTPL, ;
SUM(INCF1.bElGnAllow) AS BELGNALLOW, ;
SUM(INCF1.bElLsAllow) AS BELLSALLOW, ;
SUM(INCF1.bElTotOpEx) AS BELTOTOPEX, ;
SUM(INCF1.bElNetOpIn) AS BELNETOPIN, ;
SUM(INCF1.bGsOpRev) AS BGSOPREV, ;
SUM(INCF1.bGsOpExp) AS BGSOPEXP, ;
SUM(INCF1.bGsMnExp) AS BGSMNEXP, ;
SUM(INCF1.bGsDepExp) AS BGSDEPEXP, ;
SUM(INCF1.bGsAmUtPl) AS BGSAMUTPL, ;
SUM(INCF1.bGsAmUPAdj) AS BGSAMUPADJ, ;
SUM(INCF1.bGsAmProp) AS BGSAMPROP, ;
SUM(INCF1.bGsAmConv) AS BGSAMCONV, ;
SUM(INCF1.bGsRegDebt) AS BGSREGDEBT, ;
SUM(INCF1.bGsRegCred) AS BGSREGCRED, ;
SUM(INCF1.bGsTaxes) AS BGSTAXES, ;
SUM(INCF1.bGsIncTaxF) AS BGSINCTAXF, ;
SUM(INCF1.bGsIncTaxO) AS BGSINCTAXO, ;
SUM(INCF1.bGsDefIncT) AS BGSDEFINCT, ;
SUM(INCF1.bGsLDefInc) AS BGSLDEFINC, ;
SUM(INCF1.bGsInvTxCr) AS BGSINVTXCR, ;
SUM(INCF1.bGsGnUtPl) AS BGSGNUTPL, ;
SUM(INCF1.bGsLsUtPl) AS BGSLSUTPL, ;
SUM(INCF1.bGsGnAllow) AS BGSGNALLOW, ;
SUM(INCF1.bGsLsAllow) AS BGSLSALLOW, ;
SUM(INCF1.bGsTotOpEx) AS BGSTOTOPEX, ;
SUM(INCF1.bGsNetOpIn) AS BGSNETOPIN, ;
SUM(INCF1.bOtOpRev) AS BOTOPREV, ;
SUM(INCF1.bOtOpExp) AS BOTOPEXP, ;
SUM(INCF1.bOtMnExp) AS BOTMNEXP, ;
SUM(INCF1.bOtDepExp) AS BOTDEPEXP, ;
SUM(INCF1.bOtAmUtPl) AS BOTAMUTPL, ;
SUM(INCF1.bOtAmUPAdj) AS BOTAMUPADJ, ;
SUM(INCF1.bOtAmProp) AS BOTAMPROP, ;
SUM(INCF1.bOtAmConv) AS BOTAMCONV, ;
SUM(INCF1.bOtRegDebt) AS BOTREGDEBT, ;
SUM(INCF1.bOtRegCred) AS BOTREGCRED, ;
SUM(INCF1.bOtTaxes) AS BOTTAXES, ;
SUM(INCF1.bOtIncTaxF) AS BOTINCTAXF, ;
SUM(INCF1.bOtIncTaxO) AS BOTINCTAXO, ;
SUM(INCF1.bOtDefIncT) AS BOTDEFINCT, ;
SUM(INCF1.bOtLDefInc) AS BOTLDEFINC, ;
SUM(INCF1.bOtInvTxCr) AS BOTINVTXCR, ;
SUM(INCF1.bOtGnUtPl) AS BOTGNUTPL, ;
SUM(INCF1.bOtLsUtPl) AS BOTLSUTPL, ;
SUM(INCF1.bOtGnAllow) AS BOTGNALLOW, ;
SUM(INCF1.bOtLsAllow) AS BOTLSALLOW, ;
SUM(INCF1.bOtTotOpEx) AS BOTTOTOPEX, ;
SUM(INCF1.bOtNetOpIn) AS BOTNETOPIN, ;
SUM(INCF1.bToOpRev) AS BTOOPREV, ;
SUM(INCF1.bToOpExp) AS BTOOPEXP, ;
SUM(INCF1.bToMnExp) AS BTOMNEXP, ;
SUM(INCF1.bToDepExp) AS BTODEPEXP, ;
SUM(INCF1.bToAmUtPl) AS BTOAMUTPL, ;
SUM(INCF1.bToAmUPAdj) AS BTOAMUPADJ, ;
SUM(INCF1.bToAmProp) AS BTOAMPROP, ;
SUM(INCF1.bToAmConv) AS BTOAMCONV, ;
SUM(INCF1.bToRegDebt) AS BTOREGDEBT, ;
SUM(INCF1.bToRegCred) AS BTOREGCRED, ;
SUM(INCF1.bToTaxes) AS BTOTAXES, ;
SUM(INCF1.bToIncTaxF) AS BTOINCTAXF, ;
SUM(INCF1.bToIncTaxO) AS BTOINCTAXO, ;
SUM(INCF1.bToDefIncT) AS BTODEFINCT, ;
SUM(INCF1.bToLDefInc) AS BTOLDEFINC, ;
SUM(INCF1.bToInvTxCr) AS BTOINVTXCR, ;
SUM(INCF1.bToGnUtPl) AS BTOGNUTPL, ;
SUM(INCF1.bToLsUtPl) AS BTOLSUTPL, ;
SUM(INCF1.bToGnAllow) AS BTOGNALLOW, ;
SUM(INCF1.bToLsAllow) AS BTOLSALLOW, ;
SUM(INCF1.bToTotOpEx) AS BTOTOTOPEX, ;
SUM(INCF1.bToNetOpIn) AS BTONETOPIN, ;
SUM(INCF1.bOiMerchRe) AS BOIMERCHRE, ;
SUM(INCF1.bOiMerchEx) AS BOIMERCHEX, ;
SUM(INCF1.bOiNURev) AS BOINUREV, ;
SUM(INCF1.bOiNuExp) AS BOINUEXP, ;
SUM(INCF1.bOiRentInc) AS BOIRENTINC, ;
SUM(INCF1.bOiEqEarn) AS BOIEQEARN, ;
SUM(INCF1.bOiIntDiv) AS BOIINTDIV, ;
SUM(INCF1.bOiAFUDC) AS BOIAFUDC, ;
SUM(INCF1.bOiMiscInc) AS BOIMISCINC, ;
SUM(INCF1.bOiGnDispP) AS BOIGNDISPP, ;
SUM(INCF1.bOiTotInc) AS BOITOTINC, ;
SUM(INCF1.bOdDispPr) AS BODDISPPR, ;
SUM(INCF1.bOdMiscAm) AS BODMISCAM, ;
SUM(INCF1.bOdMiscDed) AS BODMISCDED, ;
SUM(INCF1.bOdTotal) AS BODTOTAL, ;
SUM(INCF1.bTxTxOInc) AS BTXTXOINC, ;
SUM(INCF1.bTxIncTxF) AS BTXINCTXF, ;
SUM(INCF1.bTxIncTxO) AS BTXINCTXO, ;
SUM(INCF1.bTxDefTx) AS BTXDEFTX, ;
SUM(INCF1.bTxDefTxCr) AS BTXDEFTXCR, ;
SUM(INCF1.bTxInTxCrA) AS BTXINTXCRA, ;
SUM(INCF1.bTxInvTxCr) AS BTXINVTXCR, ;
SUM(INCF1.bTxTotal) AS BTXTOTAL, ;
SUM(INCF1.bNetOtIncD) AS BNETOTINCD, ;
SUM(INCF1.bInIntLTD) AS BININTLTD, ;
SUM(INCF1.bInAmDisc) AS BINAMDISC, ;
SUM(INCF1.bInAmReaq) AS BINAMREAQ, ;
SUM(INCF1.bInLsAmPr) AS BINLSAMPR, ;
SUM(INCF1.bInLsAmGn) AS BINLSAMGN, ;
SUM(INCF1.bInIntDebt) AS BININTDEBT, ;
SUM(INCF1.bInOtIntEx) AS BINOTINTEX, ;
SUM(INCF1.bInLsAFUDC) AS BINLSAFUDC, ;
SUM(INCF1.bInNetInt) AS BINNETINT, ;
SUM(INCF1.BeXAFTERTX) AS BEX_AFTERTX, ;
SUM(INCF1.bExIncTxFO) AS BEXINCTXFO, ;
SUM(INCF1.bNetIncome) AS BNETINCOME, ;
SUM(ENERBALIOU.bTotGener - ;
ENERBALIOU.bLessPump + ;
ENERBALIOU.bPurchases + ;
ENERBALIOU.bExchgIn - ;
ENERBALIOU.bExchgOut + ;
ENERBALIOU.bWheelIn - ;
ENERBALIOU.bWheelOut + ;
ENERBALIOU.bWheelByLoss) AS ERROR1, ;
SUM(ENERBALIOU.bRetailSale) AS RETSALEIOU, ;
SUM(ENERBALIOU.bWholesaleReq) AS WLSALRQIOU, ;
SUM(ENERBALIOU.bWholesaleNon) AS WLSALNOIOU, ;
SUM(ENERBALIOU.bWholesaleReq+;
ENERBALIOU.bWholesaleNon) AS ERROR2, ;
SUM(ENERBALIOU.bRetailsale+;
ENERBALIOU.bWholesaleReq+;
ENERBALIOU.bWholesaleNon) AS ERROR3, ;
SUM(ENERBALIOU.bEnerUsed) AS ENRUSEDIOU, ;
SUM(ENERBALIOU.bEnerLoss) AS ENERLOSIOU, ;
SUM(ENERBALIOU.bRetailSale+;
ENERBALIOU.bWholesaleReq+;
ENERBALIOU.bWholesaleNon+;
ENERBALIOU.bEnerNoChg+;
ENERBALIOU.bEnerUsed+;
ENERBALIOU.bEnerLoss) AS ERROR4 ;
FROM ;
INCF1 INCF1, ;
ENERBALIOU ENERBALIOU ;
WHERE ;
INCF1.UKEY = ENERBALIOU.UKEY AND ;
INCF1.UKEY = "00000000001004642001" ;
GROUP BY ;
1, ;
2 ;
INTO TABLE 'results'