Quote:
SELECT ilv1_1.gcb gcb,
ILV1_1.rating RATING,
ILV1_1.person_seq_id,
ilv1_2.person_seq_id,
ILV1_2.bonus_amount
FROM (SELECT lt.lookup_cd gcb,
pr.perf_rating_amount RATING,
h.person_seq_id
FROM fr_employees h,
fr_emp_salaries es,
fr_periods p,
fr_lookup_types lt,
fr_emp_details ed,
fr_emp_performances ep,
fr_perf_ratings pr
WHERE p.period_status = 'CURRENT'
AND ed.period_seq_id = p.period_seq_id
AND es.period_seq_id = p.period_seq_id
AND ep.period_seq_id = p.period_seq_id
AND ed.person_seq_id = h.person_seq_id
AND ed.person_seq_id = ep.person_seq_id
AND ed.person_seq_id = es.person_seq_id
AND Substr(ed.pay_review_type, 4, 1) = 'D'
AND es.global_grade_id = lt.lookup_type_seq_id
AND ep.perf_rating_seq_id_1 = pr.perf_rating_seq_id
AND Nvl(pr.perf_rating_amount, 0) > 0
AND ( Nvl(ed.excluded_flag, 'N') = 'N' )
AND h.attribute1 = '30029767 - Günther, Marc Sascha'
AND h.attribute2 = '33000046 - Guzman, Jeevan James'
AND h.attribute3 IS NULL) ILV1_1,
(SELECT h.person_seq_id,--h.last_name,
Sum(Nvl(bonus_amount, 0) * cr.cur_exchange_rt) bonus_amount
FROM fr_employees h,
fr_emp_details ed,
fr_emp_bonuses eb,
fr_bonus_types bt,
fr_currency_rates cr,
fr_periods p
WHERE p.period_status = 'CURRENT'
AND ed.period_seq_id = p.period_seq_id
AND eb.period_seq_id = p.period_seq_id
AND cr.period_seq_id = p.period_seq_id
AND eb.person_seq_id = ed.person_seq_id
AND h.person_seq_id = ed.person_seq_id
AND eb.bonus_type_seq_id = bt.bonus_type_seq_id
AND bt.summary_total_type IN ( 'D', 'E', 'G', 'H' )
AND Nvl(eb.bonus_amount, 0) > 0
AND CR.from_curr_cd = EB.currency_cd
AND cr.to_curr_cd = 'USD'
AND ( Nvl(ed.excluded_flag, 'N') = 'N' )
AND h.attribute1 = '30029767 - Günther, Marc Sascha'
AND H.attribute2 = '33000046 - Guzman, Jeevan James'
AND H.attribute3 IS NULL
GROUP BY h.person_seq_id --,H.LAST_NAME
) ilv1_2
WHERE ILV1_1.person_seq_id = ILV1_2.person_seq_id (+)
The first inline view (ilv1_1) gives 7 records (7 employees) and 2nd inline view (ilv1_2) gives 5 records (5 employees). Based on the final OUTER JOIN, I get following result. This result is wrong. There are only 5 records matching. In ID_1 column, I am expecting two blank values corresponding to two blank amounts. 