capture duplicate data

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

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

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

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

[DEEPTI]

Hope this might help you

viewtopic.php?t=115596&highlight=capture+duplicates

Thanks,
Deepti

[ray.wurlod]

Search - this question has been answered at least twice in the past.

[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

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

[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