Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Set relationship
Message
 
Information générale
Forum:
Visual FoxPro
Catégorie:
Codage, syntaxe et commandes
Versions des environnements
Visual FoxPro:
VFP 9 SP2
OS:
Windows 7
Network:
Windows 2008 Server
Database:
MS SQL Server
Application:
Desktop
Divers
Thread ID:
01593576
Message ID:
01593626
Vues:
60
>>>>>>Hello
>>>>>>I have three table with a common field. Can I set the relation between these three tables and their common field? I am used to doing
>>>>>>this with 2 but not 3. Is there some way I can establish a "relationship" between 12 tables, each of which has a common field.
>>>>>
>>>>>I find using SQL and cursors much easier than anything involving SET RELATION, SET SKIP etc. I haven't used the latter commands for many years.
>>>>
>>>>I still use them to verify in first step visually if I generated large amounts of data - second step is generating descriptive stats across linked tables. Often you see your gotchas in the browse relationships test without having to wait for heavy join action ;-)
>>>
>>>I have 12 tables, actually more, and each one has a common field, called mDate, in this format: 201211281730, which includes the year, the month, the day, the hour, and the
>>>minute (in 15 minute segments). In addition, each table has a numeric field, called mRating. What I really want to do is to get the sum of all of the mRating's fields, for each particular
>>>hour. I really am not sure how to do this with sql. The following must be a step in the wrong direction and will take a really, really long time.
>>>
>>>
>>>SELECT table1.mRating + table2.mRating as SumRating FROM table1, table2
>>>
>>>
>>>Is there a better quicker way to do this with SQL? I know I can use set relation with 2 tables. But the help in VFP 9 does not say much about additive, although I
>>>suppose it must be what it sounds like.
>>>
>>>Thanks, Steve
>>
>>
>>CLOSE ALL
>>SELECT 1
>>USE table4
>>INDEX on mindex TO g
>>SELECT 2
>>USE table5
>>INDEX on mindex TO gg
>>SELECT 3
>>USE table6
>>INDEX on mindex TO ggg
>>SELECT 4
>>USE table7
>>INDEX on mindex TO gggg
>>SELECT 5
>>USE table8
>>INDEX on mindex TO o
>>SELECT 6
>>USE table9
>>INDEX on mindex TO oo
>>
>>SELECT 1
>>SET RELATION TO mindex INTO table5 additive
>>SET RELATION TO mindex INTO table6 additive
>>SET RELATION TO mindex INTO table7 additive
>>SET RELATION TO mindex INTO table9 additive
>>SET RELATION TO mindex INTO table10 additive  && after 4 set relation's, foxpro says the following: Table10 is not found
>>
>
>Can you explain exactly what do you want to do? The code above is very bad and it should not be used in the real program.
>
>I think you want something along these lines:
>
>select NVL(T1.RatingSum,0) + NVL(T2.RatingSum) as TotalRating, NVL(T1.yymmddhh, T2.yymmddhh) AS yyMMddhh
>
>FROM (select sum(mRating) as RatingSum, LEFT(mDate,10)  as yyMMddhh  from Table1 GROUP BY 2) T1
>
>FULL JOIN (select sum(mRating) as RatingSum, LEFT(mDate,10)  as yyMMddhh  from Table2 GROUP BY 2) T2
>
>ON T1.yymmddhh = T2.yymmddhh
>
>You can get your sums from each table into cursors and then join all these cursors together for your final result to get total sum.

Naomi, I have the following questions about your SQL query and want to thank you, Steve
select  ;
NVL(T1.RatingSum,0) + NVL(T2.RatingSum) as TotalRating, ;
NVL(T1.yymmddhh, T2.yymmddhh)  AS yyMMddhh	;
FROM  ;
(  ;
select ;   && is this 1st select supposed to be one of the tables that FROM looks at?
sum(mRating) 	as RatingSum,	;
LEFT(mDate,10)  as yyMMddhh	        ;
from Table1 	                                        ;
GROUP BY 2						;  && what does Group by 2 mean?
) 	 ;
T1	 ;  && doesn't there have to be a "," between T1 and the "table" before it?
FULL JOIN  ;
(	;
select  ;
sum(mRating) 	as RatingSum, ;
LEFT(mDate,10)  as yyMMddhh  	;
from Table2 	;
GROUP BY 2						;  && again?
) 	;
T2	;  && no "," between T2 and "table" before it?
ON T1.yymmddhh = T2.yymmddhh		&& use ON instead of WHERE, I guess
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform