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?
 Borislav Borissov
 To: Jefferson Aguilar
  Where is Jefferson Aguilar?
 Jefferson Aguilar
Subject: RE: SQL - summary
Thread ID: 78913 Message ID: 78928 # Views: 1 # Ratings: 0
Version: Visual FoxPro 8 Category: Databases, Tables and SQL Server
Date: Tuesday, October 11, 2005 8:53:35 AM         

> aloha experts,
> i have a cursor. each record has a corresponding summary, wherein, the summary will come from another table that will match my condition. to make it clearer, this is my routine (although i know that there is a shorter but more effective way on doing this):
*!* 1st I have to get all the data that i want to display
> SELECT * FROM MyTable1 INTO CURSOR Temp01 WHERE MyCondition1 = .T. AND MyCondition2 = .T. AND SoOn = .T. READWRITE
> *!* Now I get the summary of each record.
> SELECT 'Temp01'
>   SELECT 'MyTable1'
>   SUM MyTable1.NumericField1 TO MySumVariable1 FOR MyScope1 = .T.
>   SUM MyTable1.NumericField2 TO MySumVariable2 FOR MyScope2 = .T.
>   SUM MyTable1.NumericField3 TO MySumVariable3 FOR MyScope3 = .T.
>   SELECT 'Temp01'
>   REPLACE Temp01.NumericField1 WITH MySumVariable1
>   REPLACE Temp01.NumericField2 WITH MySumVariable2
>   REPLACE Temp01.NumericField3 WITH MySumVariable3

> meaning, if i have three(3) fields that require summaries, i'm obliged to issue SUM command which really takes time to be executed..please take note also that i don't put all of them in a single line - SELECT SQL command and insert a SUM(Condition) keyword, for each summary contains different conditions..

I consider that MyScope1..3 are related with Temp01 (if not then the whole loop doesn't make a sence)
What happens if you just use:

SELECT [field_list_here_without_NumericField),;
       SUM(IIF(MyScope1,NumericField1,00000000000.00)) AS NumericField1,;
       SUM(IIF(MyScope2,NumericField2,00000000000.00)) AS NumericField2,;
       SUM(IIF(MyScope3,NumericField3,00000000000.00)) AS NumericField3;
FROM MyTable1;
WHERE MyCondition1 AND MyCondition2 AND SoOn;


SQL - summary Posted by Jefferson Aguilar @ 10/11/2005 3:21:59 AM
RE: SQL - summary Posted by Borislav Borissov @ 10/11/2005 8:53:35 AM