>>>>>>NVL(T1.RatingSum,0) + NVL(T2.RatingSum) as TotalRating,; && too few arguments >>>>>>>>>>>
>>>>select ; >>>>NVL(T1.RatingSum,0) + NVL(T2.RatingSum,0) as TotalRating, ; >>>>NVL(T1.mDate, T2.mDate) AS yyMMddhh ; >>>>FROM ; >>>>( select ; >>>>MAX(mRating) as RatingSUM ,; &&&&&&&& "syntax error" ??? >>>>substr(CAST(mIndex as C(14)),1,6) as mDate ; >>>>from tab1 ; >>>>GROUP BY 2 >>>>) AS T1 ; >>>>FULL JOIN ; >>>>(select ; >>>>MAX(mRating) as RatingSUM ,; >>>>substr(CAST(mIndex as C(14)),1,6) as mDate ; >>>>from tab2 ; >>>>GROUP BY 2 >>>>) ; >>>>T2 ; >>>>ON T1.mDate = T2.mDate >>>>>>>
>>select ; >>NVL(T1.RatingMax,0) + NVL(T2.RatingMAX,0) as SumMAXs, ; >>NVL(T1.mDate, T2.mDate) AS yyMMddhh ; >>FROM ; >>( ; >>select ; && this select works fine >>MAX(mRating) as RatingMAX ,; && still get syntax error here >>substr(CAST(mIndex as C(14)),1,6) as yyMMddhh ; >>from t1 ; >>GROUP BY 2 >>) ; >>T1 ; >>FULL JOIN ; >>( ; >>select ; && this select works fine >>MAX(mRating) as RatingMAX ,; >>substr(CAST(mIndex as C(14)),1,6) as yyMMddhh ; >>from t2 ; >>GROUP BY 2 >>) ; >>T2 ; >>ON T1.yymmddhh = T2.yymmddhh >>>
select ; NVL(T1.RatingMax,0) + NVL(T2.RatingMAX,0) as SumMAXs, ; NVL(T1.yyMMddhh, T2.yyMMddhh) AS yyMMddhh ; FROM; T1 ; FULL JOIN ; T2 ; ON T1.yymmddhh = T2.yymmddhhIs there a way to make the output look like this, that is to include the columns for the corresponding maxes for T1 and T2.
TheDate MaxT1 MaxT2 SumMAXs 201301 2.5 1.3 3.8this produces an error, for example
select ; mRating ,; && tried to add in the mRating field, so it would be in the result MAX(mRating) as RatingMAX ,; substr(CAST(mIndex as C(14)),1,6) as yyMMddhh ; from tab1 ; GROUP BY 3 && tried to change this to 3 since want to group on 3rd column which is yyMMdd?This works:
select ; MAX(mRating) as RatingMAX ,; substr(CAST(mIndex as C(14)),1,6) as yyMMddhh ; from tab1 ; GROUP BY 2