>>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 >>>
>>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 10The output is like this now, and is something that I can easily automate into excel: I started with T4 because it has more dates,
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