Page 1 of 1
capture duplicate data
Posted: Wed Nov 19, 2008 2:41 am
by rmeenakumari822
Hi,
1. I have a source like
id name sal
1 aaa 1000
2 bbb 2350
1 aaa 1700
3 ccc 3690
1 aaa 4000
2 bbb 3000
in target duplicate records must be caputure
1 aaa 1000
1 aaa 1700
2 bbb 2350
How to solve it?
2. How to capture only the Duplicate record for source using SQL query?
Re: capture duplicate data
Posted: Wed Nov 19, 2008 2:44 am
by chandrababu.Datastage
[quote="rmeenakumari822"]Hi,
1. I have a source like
id name sal
1 aaa 1000
2 bbb 2350
1 aaa 1700
3 ccc 3690
1 aaa 4000
2 bbb 3000
in target duplicate records must be caputure
1 aaa 1000
1 aaa 1700
2 bbb 2350
How to solve it?
2. How to capture only the Duplicate record for source using SQL query?[/quoteUse remove duplicates stage and get rejects as your output..
Re: capture duplicate data
Posted: Wed Nov 19, 2008 3:16 am
by rmeenakumari822
Hi,
by using remove duplicate stage duplicate data will be removed but i want to caputured the duplicate data to one target table how?
Re: capture duplicate data
Posted: Wed Nov 19, 2008 3:17 am
by rmeenakumari822
Hi,
by using remove duplicate stage duplicate data will be removed but i want to caputured the duplicate data to one target table
how to solve it?
Re: capture duplicate data
Posted: Wed Nov 19, 2008 3:30 am
by DEEPTI
Posted: Wed Nov 19, 2008 3:31 am
by ray.wurlod
Search - this question has been answered at least twice in the past.
Capture duplicate data
Posted: Wed Jan 28, 2009 11:00 pm
by fareeda_b
Hi ,
SELECT COUNT(1), COLUMN_NAME
FROM TABLE_NAME
GROUP BY COLUMN_NAME
HAVING COUNT(1) > 1
hope this might help you.
thanks
fareeda
Posted: Thu Jan 29, 2009 12:05 am
by hemanthakumar
Hi,
Use the following stages.
Source----Aggregator----Transformer----Tgt1
|
|
Tgt2
In Aggregator group by id,count(id) then for Transaformer stage take targets then in Constraint write count > 1 to Tgt1, otherwise for Tgt2.It will work.
Posted: Thu Jan 29, 2009 1:44 am
by ray.wurlod
But it will not preserve the original rows. This design will. Filter on count < 4.
Code: Select all
source ---> Copy ---> Join ---> Filter ---> target
| ^
| |
+--> Aggregator