Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

duplicates in data - oracle forms built with where clause

Posted on 2012-08-20
11
Medium Priority
?
684 Views
Last Modified: 2012-08-20
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
0
Comment
Question by:anumoses
  • 6
  • 4
11 Comments
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 38311528
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
 
LVL 6

Author Comment

by:anumoses
ID: 38311539
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38311550
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 38

Accepted Solution

by:
Geert Gruwez earned 500 total points
ID: 38311665
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38312119
>>Its database issue.

What was the issue?
0
 
LVL 6

Author Comment

by:anumoses
ID: 38312153
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38312175
So it was duplicate data?
0
 
LVL 6

Author Comment

by:anumoses
ID: 38312184
yes.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38312195
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38312198
then http:#a38311665 "maybe someone duplicated records in system_users@pdon_new"
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 38312233
thanks
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month14 days, 3 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question