>>>>>>>>Should I be able to test each of the selects separately, starting from the bottom? In the top select, does the line
>>>>>>>>
>>>>>>>>NVL(T1.RatingSum,0) + NVL(T2.RatingSum) as TotalRating, ;
>>>>>>>>
>>>>>>>>presuppose that a field named RatingSum already exists, or can it be a field that is defined in this query
>>>>>>>>Thanks
>>>>>>>>Steve
>>>>>>>
>>>>>>>To test, start from the query defined in parenthesis and run it with output to a cursor and then browse that cursor (remember my suggestion in your other thread).
>>>>>>>
>>>>>>>In fact, I think it will be easier for you to first run all your sums into cursors and only after you get the intermediate data for each hour and verify them, join all these cursors together and get the final result you're after.
>>>>>>
>>>>>>OK. I have the two bottom selects working, but the top one still produces an error in the second line which is:
>>>>>>
>>>>>>

>>>>>>NVL(T1.RatingSum,0) + NVL(T2.RatingSum) as TotalRating,;    && too few arguments
>>>>>>

>>>>>
>>>>>In the second NVL expression you're missing second argument 0.
>>>>
>>>>

>>>>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
>>>>

>>>
>>>
>>>I made one obvious correction and I don't see other errors except for the fact you're selecting MAX of the ratings but calling it RatingSum and then get a total of 2 max ratings.
>>>
>>>I think the intention is to get SUM of all ratings, not the sum of two maximum ratings? Or at least use correct column names to reflect what you're doing.
>>
>>

>>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
>>

>
>I don't see the error. I just want to point out that 6 first characters will be just yearmonth, not year month day hour.
>
>I tried this in the command window
>
>mIndex = 201311242210
>?substr(CAST(mIndex as C(14)),1,6)
>
>and didn't get any error.

I ran the two bottom selects and saved them to tables: T1 and T2. And the following now works! Thank you! This is a big
shift for someone who was firmly wedded to set relation, etc.

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.yymmddhh

Is there a way to make the output look like this, that is to include the columns for the corresponding maxes for T1 and T2.
I tried adding in fields to my two bottom selects, but when I try, it isn't working, maybe because there is a MAX in there
and also a GROUP BY?

TheDate  MaxT1  MaxT2  SumMAXs
201301    2.5       1.3        3.8

this 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