SELECT Statement

There are two different SUM statemtns. One is a VFP command meant to be
used as a modifier, such as a SCAN or a REPLACE. The other is a SQL
function. You want to put your condition as part of the WHERE clause of
your SELECT, not as a FOR clause. Two very diferent animals.

dToday = DATE()
SELECT GLr1.acc_num,;
SUM(GLr_hist.crdt-GLr_hist.dbt) AS BAL1;
FROM GLr1 INNER JOIN GLr_hist;
ON GLr1.acc_num = GLr_hist.acc_num;
WHERE Glr_hist.date1 < dToday ;
GROUP BY GLr1.acc_num;
INTO CURSOR YTDResults

I've also changed the use of the DATE() function to store it into a variable
to keep the function from being evaluated for every record of your SELECT.

--

Fred
Microsoft Visual FoxPro MVP
Please respond only to the newsgroups so that all may benefit.

LouisC wrote:
> Hello, I will like to know why the FOR statement for the SUM function
> does
> not work on this SELECT Statement. It works fine when I remove the
> FOR statement.
>
> &&THIS WORKS FINE
> SELECT GLr1.acc_num,;
> SUM(GLr_hist.crdt-GLr_hist.dbt) AS BAL1;
> FROM GLr1 INNER JOIN GLr_hist;
> ON GLr1.acc_num = GLr_hist.acc_num;
> GROUP BY GLr1.acc_num;
> INTO CURSOR YTDResults
>
> &&THIS DOES NOT WORK, WHY?
> SELECT GLr1.acc_num,;
> SUM(GLr_hist.crdt-GLr_hist.dbt) FOR Glr_hist.date1 < Date() AS BAL1;
> FROM GLr1 INNER JOIN GLr_hist;
> ON GLr1.acc_num = GLr_hist.acc_num;
> GROUP BY GLr1.acc_num;
> INTO CURSOR YTDResults
>
> Please help.
>
> Thanks, Louis

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.509 / Virus Database: 306 - Release Date: 8/12/2003

Actually, there is a third. The SUM statement. It can do essentially the
same thing as the SUM() function in SQL SELECT, but it's the old xbase
style.

SELECT YourTable
SUM YourField FOR condition

But I guess, you already knew that one since that was the syntax you tried
to use.

--

Fred
Microsoft Visual FoxPro MVP
Please respond only to the newsgroups so that all may benefit.

LouisC wrote:
> Thanks Fred, that helps a lot.
>
> Louis
>
> "Fred Taylor" <ftaylor@mvps.org> wrote:
>> There are two different SUM statemtns. One is a VFP command meant
>> to be used as a modifier, such as a SCAN or a REPLACE. The other is
>> a SQL function. You want to put your condition as part of the WHERE
>> clause of your SELECT, not as a FOR clause. Two very diferent
>> animals.
>>
>> dToday = DATE()
>> SELECT GLr1.acc_num,;
>> SUM(GLr_hist.crdt-GLr_hist.dbt) AS BAL1;
>> FROM GLr1 INNER JOIN GLr_hist;
>> ON GLr1.acc_num = GLr_hist.acc_num;
>> WHERE Glr_hist.date1 < dToday ;
>> GROUP BY GLr1.acc_num;
>> INTO CURSOR YTDResults
>>
>> I've also changed the use of the DATE() function to store it into a
>> variable to keep the function from being evaluated for every record
>> of your SELECT.
>>
>>
>> --
>>
>> Fred
>> Microsoft Visual FoxPro MVP
>> Please respond only to the newsgroups so that all may benefit.
>>
>>
>>
>> LouisC wrote:
>>> Hello, I will like to know why the FOR statement for the SUM
>>> function does
>>> not work on this SELECT Statement. It works fine when I remove the
>>> FOR statement.
>>>
>>> &&THIS WORKS FINE
>>> SELECT GLr1.acc_num,;
>>> SUM(GLr_hist.crdt-GLr_hist.dbt) AS BAL1;
>>> FROM GLr1 INNER JOIN GLr_hist;
>>> ON GLr1.acc_num = GLr_hist.acc_num;
>>> GROUP BY GLr1.acc_num;
>>> INTO CURSOR YTDResults
>>>
>>> &&THIS DOES NOT WORK, WHY?
>>> SELECT GLr1.acc_num,;
>>> SUM(GLr_hist.crdt-GLr_hist.dbt) FOR Glr_hist.date1 < Date() AS
>>> BAL1; FROM GLr1 INNER JOIN GLr_hist;
>>> ON GLr1.acc_num = GLr_hist.acc_num;
>>> GROUP BY GLr1.acc_num;
>>> INTO CURSOR YTDResults
>>>
>>> Please help.
>>>
>>> Thanks, Louis
>>
>>
>> --
>> Outgoing mail is certified Virus Free.
>> Checked by AVG anti-virus system (http://www.grisoft.com).
>> Version: 6.0.509 / Virus Database: 306 - Release Date: 8/12/2003

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.509 / Virus Database: 306 - Release Date: 8/12/2003