create or replace view RE
as
select
	lb.shortname_tx as bases
	,e.outs_ct as outs
	,count(1) as PA
	,sum(e.event_runs_ct+e.fate_runs_ct) as R
	,round(sum(e.event_runs_ct+e.fate_runs_ct)/count(1),3) as reoi
	,round(sum(decode(e.event_runs_ct+e.fate_runs_ct,0,1,0))/count(1),3) as reoi_0
	,round(sum(decode(e.event_runs_ct+e.fate_runs_ct,1,1,0))/count(1),3) as reoi_1
	,round(sum(decode(e.event_runs_ct+e.fate_runs_ct,2,1,0))/count(1),3) as reoi_2
	,round(sum(decode(e.event_runs_ct+e.fate_runs_ct,3,1,0))/count(1),3) as reoi_3
	,round(sum(decode(e.event_runs_ct+e.fate_runs_ct,4,1,0))/count(1),3) as reoi_4
	,round(sum(decode(e.event_runs_ct+e.fate_runs_ct,5,1,0))/count(1),3) as reoi_5
	,round(sum(decode(e.event_runs_ct+e.fate_runs_ct,6,1,0))/count(1),3) as reoi_6
	,round(sum(decode(e.event_runs_ct+e.fate_runs_ct,7,1,0))/count(1),3) as reoi_7
	,round(sum(decode(e.event_runs_ct+e.fate_runs_ct,8,1,0))/count(1),3) as reoi_8
	,round(sum(decode(e.event_runs_ct+e.fate_runs_ct,9,1,0))/count(1),3) as reoi_9
	,round(sum(case when e.event_runs_ct+e.fate_runs_ct >= 10 then 1 else 0 end)/count(1),3) as reoi_10plus

from
	events e
	inner join
	lkup_cd_bases lb
		on e.start_bases_cd = lb.value_cd
--	inner join
--	events_ext ex
--		on e.seq_events = ex.seq_events
	left join
	events_inning_partial eip
		on e.game_id = eip.game_id
		and e.bat_team_id = eip.bat_team_id
		and e.inn_ct = eip.inn_ct

where	e.pa_new_fl = 'T'
and	eip.game_id is null
and	(e.inn_ct <= 8 or e.bat_home_id = 0)

group by
	e.start_bases_cd
	,lb.shortname_tx
	,e.outs_ct

order by 
	e.start_bases_cd
	,e.outs_ct
;