Using Case Expression-----tuning

[souj]

Can somebody help rewrite this query using a CASE expression to make it work faster...............now the query is taking 11 minutes to fetch results......
----------------------------------------------------------------------------------------------------------
select
substr((select bu.pref_verdi from t_preferanse bu where bu.pref_id = 'OPERATION'),1,15) as BUNIT,
SUM(MMSCF_FLARE) MMSCF_FLARE, SUM(MMSCF_INJ) MMSCF_INJ, SUM(BBL_Oil_Fuel) BBL_Oil_Fuel,
SUM(MMSCF_FUEL) MMSCF_FUEL, v_year, rtrim(v_month) v_month
from
(
-- MMSCF of gas flared or vented from the field during the month
-- 1. MMSCF_FLARE
select
'' as BUNIT, SUM(NET_GAS_VOL_MSCF)/1000 MMSCF_FLARE, 0 MMSCF_INJ, 0 BBL_Oil_Fuel, 0 MMSCF_FUEL,
to_char(production_day,'YYYY') v_year, to_char(production_day,'MONTH') v_month
from
RV_CT_STRM_DAY_STREAM
where
STREAM_CATEGORY = 'GAS_FLARE'
group by
to_char(production_day,'YYYY'), to_char(production_day,'MONTH')
union
-- MMSCF of gas produced from the field and reinjected either
-- into the same field or other fields. Does not include
-- gas-lift gas
-- 2. MMSCF_INJ
select
'', 0, SUM(NET_GAS_VOL_MSCF)/1000, 0, 0, to_char(production_day,'YYYY'), to_char(production_day,'MONTH')
from
RV_CT_STRM_DAY_STREAM
where
STREAM_CATEGORY = 'GAS_INJ'
group by
to_char(production_day,'YYYY'), to_char(production_day,'MONTH')
union
-- Barrels of Oil (at standard conditions) used as fuel
-- from the field during the month
-- 3. BBL_Oil_Fuel
select
'', 0, 0, SUM(NET_OIL_VOL_BBLS), 0, to_char(production_day,'YYYY'), to_char(production_day,'MONTH')
from
RV_CT_STRM_DAY_STREAM
where
STREAM_CATEGORY = 'OIL_FUEL'
group by
to_char(production_day,'YYYY'), to_char(production_day,'MONTH')
union
-- MMSCF of gas used as fuel from the field during the month
-- 4. MMSCF_FUEL
select
'', 0, 0, 0, SUM(NET_GAS_VOL_MSCF)/1000, to_char(production_day,'YYYY'), to_char(production_day,'MONTH')
from
RV_CT_STRM_DAY_STREAM
where
STREAM_CATEGORY = 'GAS_FUEL'
group by
to_char(production_day,'YYYY'), to_char(production_day,'MONTH')
)
group by v_year, rtrim(v_month)
/