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
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?



    per_spinal_point_placements_f psppf
    per_all_assignments_f paaf on psppf.assignment_id = paaf.assignment_id
    per_grades pg on paaf.grade_id = pg.grade_id
   per_spinal_point_steps_f pspsf on psppf.step_id = pspsf.step_id
    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 
    psppf.assignment_id = 1111
    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: 65086
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.

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
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.


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

Current Time: Tue Jul 25 07:47:05 CDT 2017

Total time taken to generate the page: 0.11092 seconds