SQL:
SELECT /*+ use_hash(a e g) */
MAX(g.first_account_entry_date),
MAX(g.request_date),
MAX(g.requested_date),
a.individual_agreement_id, --degree
a.cal_date_id, --degree
a.life_event_type_id, --degree
MAX(e.renewal_indicator),
MAX(e.account_entry_date),
MAX(e.status),
MAX(e.risk_category),
MAX(e.inception_date),
MAX(e.issue_date),
MAX(e.planned_end_date),
MAX(e.selling_channel_role_player_id),
MAX(e.external_reference),
MAX(e.branch_company_id),
MAX(e.department_id),
MAX(e.section_office_id),
MAX(f.external_reference),
MAX(e.selling_channel_type),
MAX(h.circ_using_code),
MAX(e.recorded_date),
MAX(e.underwritting_period),
MAX(a.population_timestamp),
MAX(a.number_of_policy),
SUM(a.premium_amount * rate_f2r) premium_amount ,
SUM(CASE
WHEN a.currency_code = '01' THEN
a.premium_amount
ELSE
0
END) premium_amount_rmb
,
SUM(CASE
WHEN a.currency_code != '01' THEN
a.premium_amount * i.rate_f2u
ELSE
0
END) premium_amount_usd
,
MAX(a.number_of_business_transaction)
,
SUM(a.saving_premium_amount * i.rate_f2r) saving_premium_amount
,
SUM(CASE
WHEN a.currency_code = '01' THEN
a.saving_premium_amount
ELSE
0
END) saving_premium_amount_rmb
,
SUM(CASE
WHEN a.currency_code != '01' THEN
a.saving_premium_amount * i.rate_f2u
ELSE
0
END) saving_premium_amount_usd
,
SUM(a.sum_insured_amount * i.rate_f2r) sum_insured_amount
,
SUM(CASE
WHEN a.currency_code = '01' THEN
a.sum_insured_amount
ELSE
0
END) sum_insured_amount_rmb
,
SUM(CASE
WHEN a.currency_code != '01' THEN
a.sum_insured_amount * i.rate_f2u
ELSE
0
END) sum_insured_amount_usd
,
SUM(a.collected_premium_amount * i.rate_f2r) collected_premium_amount
,
SUM(CASE
WHEN a.currency_code = '01' THEN
a.collected_premium_amount
ELSE
0
END) collected_premium_amount_rmb
,
SUM(CASE
WHEN a.currency_code != '01' THEN
a.collected_premium_amount * i.rate_f2u
ELSE
0
END) collected_premium_amount_usd
,
SUM(a.payment_amount * rate_f2r) payment_amount
,
SUM(CASE
WHEN a.currency_code = '01' THEN
a.payment_amount
ELSE
0
END) payment_amount_rmb
,
SUM(CASE
WHEN a.currency_code != '01' THEN
a.payment_amount * i.rate_f2u
ELSE
0
END) payment_amount_usd
,
MAX(a.number_of_insured_object)
,
MAX(e.number_of_covered_person)
FROM policy_underwriting_fact a, -- 2680920 rows
individual_agreement_snapshot e, -- 2412020 rows
financial_service_product f, -- 1178 rows
agreement_request g, -- 2846620 rows
internal_organisation h, -- 14778 rows
bs_fe_exchange_rate i -- 174 rows
WHERE a.individual_agreement_id = e.individual_agreement_id
AND a.agreement_request_id = g.agreement_request_id(+)
AND a.financial_service_product_id = f.financial_service_product_id
AND e.department_id = h.internal_organisation_id
AND a.branch_company_id = e.branch_company_id
AND a.branch_company_id = g.branch_company_id(+)
AND a.branch_company_id = 1000004528
AND a.currency_code = i.base_currency
AND a.cal_date_id BETWEEN to_number(to_char(i.start_date, 'yyyymmdd')) AND
to_number(to_char(i.end_date, 'yyyymmdd'))
GROUP BY a.individual_agreement_id, --degree
a.cal_date_id, --degree
a.life_event_type_id, --degree
a.financial_service_product_id,
a.agreement_request_id,
a.external_organisation_id,
a.internal_organisation_id;