Home » SQL & PL/SQL » SQL & PL/SQL » Getting duplicates from pay_grade_rules_f (Oracle 9)
Getting duplicates from pay_grade_rules_f [message #428697] Thu, 29 October 2009 09:04 Go to next message
derrywriter
Messages: 8
Registered: October 2009
Junior Member
Hi all,

I am trying to get the salary history for an employee but I am picking up more rows that expected.

I have the code below. I know that an employee has had 6 salary amounts since they started. The code below brings me back 6 rows including start and end date for each salary.

However, when I comment in a join to pay_grade_rules_f and add in pgrf.value field in the select and attempt to get back the actual salary amount associated with each band, I am getting 12 rows back instead of 6. I think it is because my join is not unique enough. I am joining to pay_grade_rules_f from per_spinal_point_steps_f on an id field but it seems the id exists more than once in the pay_grade_rules_f table.

Would anyone know how I can fine tune this so that I only get back the correct salary for each band?

tia

dw


select
     psppf.assignment_id
    ,psppf.effective_start_date
    ,psppf.effective_end_date 
    --,pgrf.value
FROM
    per_spinal_point_placements_f psppf
join
    per_all_assignments_f paaf on psppf.assignment_id = paaf.assignment_id
join 
    per_grades pg on paaf.grade_id = pg.grade_id
join
   per_spinal_point_steps_f pspsf on psppf.step_id = pspsf.step_id
join 
    per_grade_spines_f pgsf on pg.grade_id = pgsf.grade_id 
-- join
--  pay_grade_rules_f pgrf on pspsf.spinal_point_id = pgrf.grade_or_spinal_point_id 
where
    psppf.assignment_id = 1111
and 
    sysdate between paaf.effective_start_date and paaf.effective_end_date
        
order by 2


[Mod-edit: Frank added [code] tags to improve readability]

[Updated on: Thu, 29 October 2009 09:07] by Moderator

Report message to a moderator

Re: Getting duplicates from pay_grade_rules_f [message #428698 is a reply to message #428697] Thu, 29 October 2009 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check your database model and constraints.
We have none of them, we can't say.

Regards
Michel
Re: Getting duplicates from pay_grade_rules_f [message #428699 is a reply to message #428697] Thu, 29 October 2009 09:09 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Please provide a test case (Create table and insert statement)
and make your code formatted.


Describe your problem well , it would really help you to get the answer expected by you.

Regards,
Ved

Previous Topic: Failed insertion of values into a table. (merged)
Next Topic: simultaneous multi-user application
Goto Forum:
  


Current Time: Tue Sep 27 12:44:51 CDT 2016

Total time taken to generate the page: 0.10642 seconds