SELECT /*comment for mv recreate */
(CASE
WHEN GROUPING(b.report_date_julian)=0 THEN b.report_date_julian
WHEN GROUPING(b.week_id)=0 THEN b.week_id
WHEN GROUPING(b.ent_period_id)=0 THEN b.ent_period_id
WHEN GROUPING(b.ent_qtr_id)=0 THEN b.ent_qtr_id
ELSE b.ent_year_id
END
) time_id,
(CASE
WHEN GROUPING(b.report_date_julian)=0 THEN 1
WHEN GROUPING(b.week_id)=0 THEN 16
WHEN GROUPING(b.ent_period_id)=0 THEN 32
WHEN GROUPING(b.ent_qtr_id)=0 THEN 64
ELSE 128
END
) period_type_id,
segment_id segment_id,
source_code_id source_code_id,
decode(grouping(a.category_id),1,-9,0,a.category_id) category_id,
a.category_id category_id_c,
sum(activities_count) activities_count,
count(activities_count) activities_count_c,
sum(responses) responses,
count(responses) responses_c,
sum(leads) leads,
count(leads) leads_c,
sum(new_opportunity_amt) new_opportunity_amt,
count(new_opportunity_amt) new_opportunity_amt_c,
sum(new_opportunity_amt_s) new_opportunity_amt_s,
count(new_opportunity_amt_s) new_opportunity_amt_s_c,
sum(booked_amt) booked_amt,
count(booked_amt) booked_amt_c,
sum(booked_amt_s) booked_amt_s,
count(booked_amt_s) booked_amt_s_c,
count(*) all_cnt,
b.report_date_julian dummy1_fr,
b.week_id dummy2_fr,
b.ent_period_id dummy3_fr,
b.ent_qtr_id dummy4_fr,
b.ent_year_id dummy5_fr,
grouping_id(b.report_date_julian,b.week_id,b.ent_period_id,b.ent_qtr_id,b.ent_year_id,segment_id,source_code_id,category_id
) dummy6_fr
FROM bim_sgmt_act_b_mv a,
FII.FII_TIME_DAY b
WHERE b.report_date = a.transaction_create_date
GROUP BY segment_id, source_code_id ,GROUPING SETS (b.report_date_julian,b.week_id,b.ent_period_id,b.ent_qtr_id,b.ent_year_id)
,rollup(category_id) -- dummy comments
[Home] [Help]