Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate Detection (Oracle 11g, Windows XP)
Duplicate Detection [message #444883] Wed, 24 February 2010 12:01 Go to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
Hi All,

I need your help in writing one query,

For every record which came yeterday in table ABC, I need to find if same record data does exists in historical records of the same table, and if match is find, I need to display all the occurances.

For example Data got inserted yesterday as
SQL RES : Col 1= "A" Col 2: "B" Col 3: "C" Date = Sysdate -1

then, I need to scan through Query results from the history where I can find if there exists same combination already.
and if so I need to display both i.e Record form yesterday and from history.

Here is the one I am using to get the base data.


with base as(
select Col 1, Col 2, Col 3,A.Create_DT
from A,B
where A.ID = B.ID
)




from,
freakAbhi
Re: Duplicate Detection [message #444884 is a reply to message #444883] Wed, 24 February 2010 12:03 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
How do you tell the difference between historical and current records?
Re: Duplicate Detection [message #444885 is a reply to message #444883] Wed, 24 February 2010 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You know your query is syntaxically invalid, so how can you use it?

2/ Why your query, once fixed, does not return the result you want?

3/ Insufficient ACTUAL information to help you more.

Regards
Michel
Re: Duplicate Detection [message #444887 is a reply to message #444883] Wed, 24 February 2010 12:09 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> I need to display both i.e Record form yesterday and from history.

For most folks, yesterday is HISTORY; so I am unclear about any difference between yesterday & history.
Re: Duplicate Detection [message #444896 is a reply to message #444883] Wed, 24 February 2010 12:42 Go to previous messageGo to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member

Replying the answers.

1/ You know your query is syntaxically invalid, so how can you use it?

== I have asked for the help to write the query, so surely
its not the whole query its just the resultset columns I want to find the duplicacy for.

2/ Why your query, once fixed, does not return the result you want?

== In the query as can be seen, none of the logic to detect duplicates is incoporated, so I need to have logic which will help me to find combination which occured yeterday and in history i.e. all days before yesterday.


3/ Insufficient ACTUAL information to help you more

Due to confidentiality terms I can not put in the actual code.
I hope you understand.But there is the query structure I have put in the With Clause, Is not it enough as in I am looking for just the logic, so if you can explain on the sample snippet, it will be of great help.



thanks,
freakabhi

Re: Duplicate Detection [message #444901 is a reply to message #444883] Wed, 24 February 2010 12:49 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>For every record which came yeterday in table ABC,
>from A,B

Please clarify how many tables are in your problem set, 1, 2, 3, more & their name(s).

Provide DDL (CREATE TABLE) for all table(s) in problem set.
Re: Duplicate Detection [message #444902 is a reply to message #444896] Wed, 24 February 2010 12:49 Go to previous messageGo to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
to explain :

suppose record occured :
combination occured yesrerday
Col 1 = "A" Col 2: "B" Col 3: "C" Date = Sysdate -1

and suppose same exist

Col 1= "A" Col 2: "B" Col 3: "C" Date < Sysdate -1
then these are the duplicates
Re: Duplicate Detection [message #444903 is a reply to message #444896] Wed, 24 February 2010 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Due to confidentiality terms I can not put in the actual code.

So post an example with anonymous table and column names that represent sufficinetly your case without over-simpliying it to be meaningless.

Regards
Michel
Re: Duplicate Detection [message #444904 is a reply to message #444902] Wed, 24 February 2010 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
freakabhi wrote on Wed, 24 February 2010 19:49
to explain :

suppose record occured :
combination occured yesrerday
Col 1 = "A" Col 2: "B" Col 3: "C" Date = Sysdate -1

and suppose same exist

Col 1= "A" Col 2: "B" Col 3: "C" Date < Sysdate -1
then these are the duplicates


Are they in the same table or not?
And you want what?
How can I know I answer the question?

Regards
Michel

[Updated on: Wed, 24 February 2010 12:51]

Report message to a moderator

Re: Duplicate Detection [message #444905 is a reply to message #444896] Wed, 24 February 2010 12:54 Go to previous messageGo to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
ok, in that case, you can consider following code


select Col 1, Col 2, Col 3,A.Create_DT
from A,B
where A.ID = B.ID



I need to find the duplicacy for Col 1, Col 2, Col 3,
A.Create_DT is date of creation of combination.

hope this helps!
Re: Duplicate Detection [message #444906 is a reply to message #444905] Wed, 24 February 2010 12:58 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>hope this helps!

Does not help me. Sad

please provide requested DETAILS!
Re: Duplicate Detection [message #444908 is a reply to message #444905] Wed, 24 February 2010 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Assuming tabA contains only current data and tabB only historical data (as you don't want to tell us):

select A, B, C from tabB
minus
select A, B, C from tabA

Regards
Michel

Re: Duplicate Detection [message #444909 is a reply to message #444883] Wed, 24 February 2010 13:04 Go to previous messageGo to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
answering the quests :

1)Are they in the same table or not?
Columns are result of join between A, b and not all are in the same table.

2)And you want what?
I want to detect the duplicates as per logic I explained
here

suppose combination occured yesrerday
Col 1 = "A" Col 2: "B" Col 3: "C" Date = Sysdate -1

and suppose same exist

Col 1= "A" Col 2: "B" Col 3: "C" Date < Sysdate -1
then these are the duplicates and both needs to be shown in output.


thanks

Re: Duplicate Detection [message #444910 is a reply to message #444883] Wed, 24 February 2010 13:06 Go to previous messageGo to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
i.e if values combination Col1, col2, col3 is present more than once, then those are duplicates.

Re: Duplicate Detection [message #444911 is a reply to message #444910] Wed, 24 February 2010 13:10 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
select Col1, col2, col3, count(*)
from a,b
having count(*) > 1
group by Col1, col2, col3
Re: Duplicate Detection [message #444912 is a reply to message #444908] Wed, 24 February 2010 13:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If there is only one table:
select A, B, C
from mytab
group by A,B,C
having max(trunc(mydate))=trunc(sysdate)-1
   and count(*) > 1

Regards
Michel
Re: Duplicate Detection [message #444913 is a reply to message #444911] Wed, 24 February 2010 13:14 Go to previous messageGo to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
this is one I already have in place :
But I just want to compare those which occured yesterday.


Given query by you will try to find out all the possible combination existing, and not only for the one which occured yesterday. Also table volume is very large so can not take the risk to scan whole table each time.
Re: Duplicate Detection [message #444914 is a reply to message #444913] Wed, 24 February 2010 13:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another solution:
select A,B,C 
from tabA
where exists (select null from tabB
              where tabB.A = tabA.A
                and ...)
Regards
Michel

[Updated on: Wed, 24 February 2010 13:16]

Report message to a moderator

Re: Duplicate Detection [message #444915 is a reply to message #444914] Wed, 24 February 2010 13:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another one:
select A,B,C 
from tabA
where (A,B,C) in (select A,B,C from tabB)

Regards
Michel
Re: Duplicate Detection [message #444916 is a reply to message #444913] Wed, 24 February 2010 13:18 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>But I just want to compare those which occured yesterday.
Nobody is stopping you from modifying it to meet your requirements.
Re: Duplicate Detection [message #444917 is a reply to message #444912] Wed, 24 February 2010 13:18 Go to previous message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member

that was awesome....I am yet to implement it but sounds good and only I can see problem with my date as not having proper index.

Previous Topic: Calculate with calculated fields
Next Topic: update query
Goto Forum:
  


Current Time: Fri Dec 09 06:13:13 CST 2016

Total time taken to generate the page: 0.11081 seconds