DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Strange SQL Result

  1. #1
    Join Date
    Feb 2006
    Posts
    10

    Strange SQL Result

    Hi All,

    Please see the following SQL Query

    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.

    GCB Rating ID ID_1 Amount
    1 3 3525030 3525030 5000
    2 3 3525029 3525029 1920000
    1 3 3525028 3525028
    6 4 3525025 3525025 7508
    5 3 3525024 3525024 0.02140019
    4 3 3525023 3525023
    3 3 3525022 3525022 200000

    When I add an extra column (h.last_name) in the second inline view (In the above query, I have commented that column in SELECT and GROUP BY clauses), I get the correct result. How is it possible?

    GCB Rating ID ID_1 Amount
    1 3 3525030 3525030 5000
    2 3 3525029 3525029 1920000
    1 3 3525028
    6 4 3525025 3525025 7508
    5 3 3525024 3525024 0.02140019
    4 3 3525023
    3 3 3525022 3525022 200000
    Last edited by rchenna; 09-03-2012 at 09:57 PM.

  2. #2
    Join Date
    Dec 2002
    Posts
    74
    You should avoid using old outer join syntax with +, use the ANSI compliant syntax of OUTER JOIN to avoid unexpected results.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width