Correcting the corrupted record

Database
KVB
Enthusiast

Correcting the corrupted record

Hi,

CT BBC(EMPNO INTEGER,ENAME VARCHAR(10),DW_EFF_DT DATE, DW_EXPR_DT DATE,CURR_IN INTEGER,RCV_IN INTEGER,COD VARCHAR(10),ADD_DT DATE)

 INS INTO BBC VALUES(1,'A','2014-01-01','2014-01-04',0,1,'T','2014-01-01');

 INS INTO BBC VALUES(1,'A','2014-01-05','2014-01-09',0,1,'I','2014-01-01');

 INS INTO BBC VALUES(1,'A','2014-01-10','2014-01-19',0,1,'T',NULL);

 INS INTO BBC VALUES(1,'A','2014-01-20','9999-12-31',1,1,'T','2014-01-01');

Here due to source error,NULL was inserted unnecessarily,now i want to correct

My output should be

1,'A','2014-01-01','2014-01-04',0,1,'T','2014-01-01'

1,'A','2014-01-05','2014-01-09',0,1,'I','2014-01-01'

1,'A','2014-01-10','9999-12-31,0,1,'T','2014-01-01'

When I am doing SEL T.*,ROW_NUMBER() OVER(PARTITION BY EMPNO,ENAME,COD ORDER BY DW_EFF_DT )  FROM BBC T

The first row is also taking into picture bcoz of partition by in rownum.Here where i got strucked.

Regards

KVB

2 REPLIES
KVB
Enthusiast

Re: Correcting the corrupted record

In the simple sens,I need to find the minimum effective date of consecutive duplicates (empno,ename,cod) only.

So after first row,it's again the third row.So I need to get first as well as third row.

Enthusiast

Re: Correcting the corrupted record

first and third row are with same group (empno,ename, cod). question is not clear. are you looking

SEL T.*, min(DW_EFF_DT) over (PARTITION BY EMPNO,ENAME,COD) from bbc T