duplicates in data - oracle forms built with where clause

Block name - CALLER_SCHEDULES Built with where clause

query data source name- caller_schedules cs, system_users@pdon_new su

where clause - cs.user_id = su.user_id
and cs.schedule_date =:sched_date.in_date

order by - order by su.user_last_name
----------------
When I run this query get duplicates. This is an old form developed by old employee not working here anymore.

select cs.user_id, su.user_last_name,cs.schedule_date,
          cs.hours_worked, cs.goal, cs.team_id
from caller_schedules cs, system_users@pdon_new su
where cs.user_id = su.user_id
and cs.schedule_date ='07-aug-2012'
order by su.user_last_name

If I use distinct will get no duplicates. Any help appreciated

There is a decode also

and cs.team_id = decode(:sched_date.team_id ,'ALL', cs.team_id ,:sched_date.team_id)
screen.JPG
LVL 6
anumosesAsked:
Who is Participating?
 
Geert GOracle dbaCommented:
maybe someone duplicated records in system_users@pdon_new

find them like this:
select * from system_users@pdon_new su
where su.user_id in (
  select su.user_id
  from system_users@pdon_new su
  group by su.user_id
  having count(*) > 1)

or you have multiple records for that date
>> begin and end time considerations ?
0
 
slightwv (䄆 Netminder) Commented:
Is that real data?  I suggest you scrub the names and company information.

About the question:  Tripple check your where clause to make sure your joins are correct.

Then check you don't have duplicate data in one or more of the tables that is causing the data to be duplicated.

If everyting is correct, can you not just use a distinct?
0
 
anumosesAuthor Commented:
yes joins are correct. Verified both the tables. I want to use distinct, but where can I use in the form? That's my problem
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
Please comment on the sensitive data and company information in the image...

I'm not a Forms person but you posted a select statement.  If the form is created from that select, can you not modify the select?
0
 
slightwv (䄆 Netminder) Commented:
>>Its database issue.

What was the issue?
0
 
anumosesAuthor Commented:
There was no primary key. So I had to create constraints. and change the form to be based on only one table that gets updated and the rest post query. Enforcing primary key at the form level also.
0
 
slightwv (䄆 Netminder) Commented:
So it was duplicate data?
0
 
anumosesAuthor Commented:
yes.
0
 
slightwv (䄆 Netminder) Commented:
re: http:#a38311528 "Then check you don't have duplicate data in one or more of the tables that is causing the data to be duplicated."
0
 
slightwv (䄆 Netminder) Commented:
then http:#a38311665 "maybe someone duplicated records in system_users@pdon_new"
0
 
anumosesAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.