DSXchange

i hav 2 databases first includes duplicates data and second one after removing duplicate data
eg first one contain 100 records and second one 90 records
and 10 are the duplicate entries, i want to find these 10 entries, how to do that?

Assuming that 'databases' mean 'tables' from your statement "i hav 2 databases...".
You can find the duplicate records without actually relating (e.g. join) the two tables, by firing the appropriate SQL statement against the table that has duplicate rows.

It would be easier to run a query against your first database with a query like:

SELECT COL1 FROM DBASE GROUP BY COL1 HAVING COUNT(*) > 1

assuming col1 is your unique key

Take the first dataset with 100 records and use the sort stage to sort and remove duplicate records.

Set Allow duplicates to true in the sort stage
We can capture the duplicate records using the create change key column option under the stage-> properties tab
This new key would have the value of 1 for the first record and the duplicate records as 0's. Use a filter stage next and filter the records which are 0's

Karine wrote:It would be easier to run a query against your first database with a query like:

SELECT COL1 FROM DBASE GROUP BY COL1 HAVING COUNT(*) > 1

assuming col1 is your unique key

COUNT DOESNT WORK IN DATASTAGE...

koolnitz wrote:Assuming that 'databases' mean 'tables' from your statement "i hav 2 databases...".
You can find the duplicate records without actually relating (e.g. join) the two tables, by firing the appropriate SQL statement against the table that has duplicate rows.

pls post that sql statement that should work in datastage...

Pls post what is your Database.
Btw, you could also google for the command

I'm assuming that your table has 3 columns with name col1, col2 and col3.
Here is a generic query that meets your requirement...

SELECT col1, col2, col3, count(*) as Count
FROM tab_name
GROUP BY col1, col2, col3
HAVING COUNT(*)>1

In the Database stage in your DS job, define four columns - col1, col2, col3 and Count.

abhay10 wrote:COUNT DOESNT WORK IN DATASTAGE...

Yes it does, when it's done right.

koolnitz wrote:I'm assuming that your table has 3 columns with name col1, col2 and col3.
Here is a generic query that meets your requirement...

SELECT col1, col2, col3, count(*) as Count
FROM tab_name
GROUP BY col1, col2, col3
HAVING COUNT(*)>1

In the Database stage in your DS job, define four columns - col1, col2, col3 and Count.

this will work fine but will show duplicate data only once even if more than 2 records are there

ray.wurlod wrote:

abhay10 wrote:COUNT DOESNT WORK IN DATASTAGE...

Yes it does, when it's done right.

ya, sry for that...

HAVING COUNT(*) = 1 gives you the 90 records and HAVING COUNT(*) > 1 gives you the other 10. Isn't that what you wanted?

You can do this within DataStage, effectively emulating the HAVING clause. Use a "fork join" with an Aggregator stage to get the count for each group, then run one copy of these results through a Filter stage based on the count being greater than 1. These (tgt2) are your duplicate rows. The other copy you run through a Remove Duplicates stage to get your unique rows including one copy of each duplicate row.
Make very sure that your partitioning is key-based on the grouping key(s).

ray.wurlod wrote:HAVING COUNT(*) = 1 gives you the 90 records and HAVING COUNT(*) > 1 gives you the other 10. Isn't that what you wanted?

You can do this within DataStage, effectively emulating the HAVING claus ...

no it will give 10 records only when each of those 10 records have occured only 2 times, suppose the same record has occured eleven times than it will give only one record since all other 9 are same, but i want all the 10 records...

The job design I posted will give you what you want.