Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Set relationship
Message
General information
Forum:
Visual FoxPro
Category:
Coding, syntax & commands
Environment versions
Visual FoxPro:
VFP 9 SP2
OS:
Windows 7
Network:
Windows 2008 Server
Database:
MS SQL Server
Application:
Desktop
Miscellaneous
Thread ID:
01593576
Message ID:
01593764
Views:
46
>>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
Previous
Reply
Map
View

Click here to load this message in the networking platform