All Forums General
srivalli_b 3 posts Joined 04/12
19 Jan 2014
dupliacate rows

Hi,
 
there are some duplicage some  rows in one table. I need to give the data fix.
 
example1:
 
customerno val1 val2 val3 val4 efft_d expy_d
1                  2   3     4     5     2014-01-31  999-12-31 
1                  2  3      4    5      2014-01-30   2014-01-28
1                  2  3      4    5      2014-01-29  2014-01-27
 
example2:
 
customerno val1 val2 val3 val4 efft_d expy_d
1                  2   3     4     5     2014-01-31  999-12-31 
1                  2  3      4    5      2014-01-30   2014-01-28
1                  2  3      4    5      2014-01-29  9999-12-31
 
example3:
 
customerno val1 val2 val3 val4 efft_d expy_d
1                  2   3     4     5     2014-01-31  999-12-31 
1                  2  3      4    5      2014-01-30   2014-01-28
1                  2  3      4    5      2014-01-29  2014-01-27
 

dnoeth 4628 posts Joined 11/04
19 Jan 2014

These are no duplicate rows. You probably have some rows violating your Primary Key, but i don't have a clue about your rules why it's wrong and how to fix it

Dieter

sgarlapa 88 posts Joined 03/13
19 Jan 2014

HI Srivalli,
I believe you are talking about the duplicate records based on your business columns leaving the audit date columns.
if that is the case you might have unique primary index defined in your target table on business columns such as
Primary index (customerno,val,val2,val3,val4). which would lead to duplicate error. please check that.
OR
if you are clear that there is not UPI, then you requirement may be to load a single record of each set of duplicate based on max of efft_dt and with expiry_dt as 9999-12-13.  use these conditions also in your load query.
 
Regards,
Sri.

gotuchintu 32 posts Joined 12/05
20 Jan 2014

since it is not a duplicate row, you can use either row_number or rank function to do that

Somnath Roy

You must sign in to leave a comment.