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:
01593764
Vues:
44
>>After a break, this has me in the ballpark. Thank you
>>
>>
>>select  ;
>>t1.RatingMax	as t1MAX,;
>>t2.RatingMax	as t2MAX,;
>>t3.RatingMax	as t3MAX,;
>>NVL(T1.RatingMax,0) + NVL(T2.RatingMAX,0) + NVL(T3.RatingMAX,0) as SumMAXs, ;
>>NVL(T1.yyMMddhh, T2.yyMMddhh)  AS yyT2		,;
>>NVL(T1.yyMMddhh, T3.yyMMddhh)  AS yymmddhh	;
>>FROM T1	 ;
>>FULL JOIN  T2	;
>>FULL JOIN  T3	;
>>ON T1.yymmddhh = T2.yymmddhh;
>>ON T1.yymmddhh = T3.yymmddhh
>>
>
>I usually put JOIN conditions right after the JOIN. Also, I think you need double NVL instead, e.g.
>
>
>>select  ;
>>t1.RatingMax	as t1MAX,;
>>t2.RatingMax	as t2MAX,;
>>t3.RatingMax	as t3MAX,;
>>NVL(T1.RatingMax,0) + NVL(T2.RatingMAX,0) + NVL(T3.RatingMAX,0) as SumMAXs, ;
>>NVL(NVL(T1.yyMMddhh, T2.yyMMddhh), T3.yymmddhh)  AS yymmddhh	;
>>FROM T1	 ;
>>FULL JOIN  T2	;
>ON T1.yymmddhh = T2.yymmddhh;
>>FULL JOIN  T3	;
>>ON T1.yymmddhh = T3.yymmddhh
>
Thanks. I couldn't figure out why my results were so off, but with the nested NVL's, it's working fine. I changed the date variable to YYMM since that's what it is displaying, based on the output of the previous queries for each table.
SET SAFETY off
select  				;
t4.RatingMax	as t4MAX	,;
t5.RatingMax	as t5MAX	,;
t7.RatingMax	as t7MAX	,;
t9.RatingMax	as t9MAX	,;
t11.RatingMax	as t11MAX	,;
t12.RatingMax	as t12MAX	,;
t1.RatingMax	as t1MAX	,;
t2.RatingMax	as t2MAX	,;
NVL(T4.RatingMax,0) +;
NVL(T5.RatingMAX,0) +;
NVL(T7.RatingMAX,0) +;
NVL(T9.RatingMAX,0) +;
NVL(T11.RatingMAX,0) +;
NVL(T12.RatingMAX,0) +;
NVL(T1.RatingMAX,0) +;
NVL(T2.RatingMAX,0) ;
as SumMAXs	, ;
NVL(;	&&7
NVL(;	&&6
NVL(;	&&5
NVL(;	&&4
NVL(;	&&3
NVL(;	&&2
NVL(;	&&1
T4.yymm		,; 	
T5.yymm)	,;	&&1
T7.yymm)	,;	&&2
T9.yymm)  	,;	&&3
T11.yymm)  	,;	&&4
T12.yymm)  	,;	&&5
T1.yymm)  	,;	&&6
T2.yymm)  	;	&&7
AS yymm	;
FROM T4	 ;
FULL JOIN  T5  ON T4.yymm = T5.yymm;
FULL JOIN  T7  ON T4.yymm = T7.yymm;
FULL JOIN  T9  ON T4.yymm = T9.yymm;
FULL JOIN  T11 ON T4.yymm = T11.yymm;
FULL JOIN  T12 ON T4.yymm = T12.yymm;
FULL JOIN  T1  ON T4.yymm = T1.yymm;
FULL JOIN  T2  ON T4.yymm = T2.yymm;
order BY t4.yymm;
into table gg
brow width 10
The output is like this now, and is something that I can easily automate into excel: I started with T4 because it has more dates,
and T1 - T2 were missing some. This is a big change from relying on set relation. Thanks again!
T4max   T5max   T7max ...   T1max   SumMAxs   YYmm
1.1       2.2   1.2   ...     NULL        4.5           201201
1.0       2.0   1.0   ...     NULL        4.0           201202
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform