OraFAQ Forum: SQL & PL/SQL » repeat the previous value to next rows

Home » SQL & PL/SQL » SQL & PL/SQL » repeat the previous value to next rows (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

repeat the previous value to next rows [message #646659]

Wed, 06 January 2016 11:15

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Dear Gurus,

I have a table where a can be null, if the null is encouted then it has to take previous non null value.

CREATE TABLE DS_TRAIN_LOGS.TEST
(
  UNIT_ID     VARCHAR2(250 BYTE),
  EVENT_DATE  DATE,
  DISTANCE    NUMBER
)

sample data as below, here the distance column are having null in forst 5 rows so it is fine , on sixth row the value is 5 and there onwards i want to populate 5 until non null value is encouter.
UNIT_ID EVENT_DATE DISTANCE

259 24/07/2007 13:55
331 26/04/2015 21:50
331 26/04/2015 21:50
259 26/05/2015 16:11
259 28/05/2015 08:42
259 28/05/2015 10:56 5
259 28/05/2015 10:58
259 28/05/2015 10:59
259 28/05/2015 10:59
259 28/05/2015 17:18
259 30/05/2015 02:25
259 30/05/2015 23:09
259 01/06/2015 20:47
259 01/06/2015 20:51
259 02/06/2015 13:17
331 03/06/2015 20:31 11
331 04/06/2015 01:54
259 06/06/2015 01:41
259 06/06/2015 01:41
259 06/06/2015 02:28
259 06/06/2015 02:28 6
259 06/06/2015 02:33
259 06/06/2015 10:10

expected result as below

UNIT_ID EVENT_DATE DISTANCE

259 24/07/2007 13:55
331 26/04/2015 21:50
331 26/04/2015 21:50
259 26/05/2015 16:11
259 28/05/2015 08:42
259 28/05/2015 10:56 5
259 28/05/2015 10:58 5
259 28/05/2015 10:59 5
259 28/05/2015 10:59 5
259 28/05/2015 17:18 5
259 30/05/2015 02:25 5
259 30/05/2015 23:09 5
259 01/06/2015 20:47 5
259 01/06/2015 20:51 5
259 02/06/2015 13:17 5
331 03/06/2015 20:31 11
331 04/06/2015 01:54 11
259 06/06/2015 01:41 11
259 06/06/2015 01:41 11
259 06/06/2015 02:28 11
259 06/06/2015 02:28 6
259 06/06/2015 02:33 6
259 06/06/2015 10:10 6

Insert into DS_TRAIN_LOGS.TEST
   (UNIT_ID, EVENT_DATE)
 Values
   ('259', TO_DATE('06/24/2015 14:27:52', 'MM/DD/YYYY HH24:MI:SS'));
Insert into DS_TRAIN_LOGS.TEST
   (UNIT_ID, EVENT_DATE)
 Values
   ('259', TO_DATE('06/24/2015 14:33:37', 'MM/DD/YYYY HH24:MI:SS'));
Insert into DS_TRAIN_LOGS.TEST
   (UNIT_ID, EVENT_DATE)
 Values
   ('259', TO_DATE('06/25/2015 16:29:24', 'MM/DD/YYYY HH24:MI:SS'));
Insert into DS_TRAIN_LOGS.TEST
   (UNIT_ID, EVENT_DATE)
 Values
   ('259', TO_DATE('06/25/2015 15:18:33', 'MM/DD/YYYY HH24:MI:SS'));
Insert into DS_TRAIN_LOGS.TEST
   (UNIT_ID, EVENT_DATE)
 Values
   ('259', TO_DATE('06/25/2015 16:29:23', 'MM/DD/YYYY HH24:MI:SS'));
Insert into DS_TRAIN_LOGS.TEST
   (UNIT_ID, EVENT_DATE)
 Values
   ('259', TO_DATE('05/28/2015 08:42:48', 'MM/DD/YYYY HH24:MI:SS'));
Insert into DS_TRAIN_LOGS.TEST
   (UNIT_ID, EVENT_DATE, DISTANCE)
 Values
   ('259', TO_DATE('05/28/2015 10:56:10', 'MM/DD/YYYY HH24:MI:SS'), 5);
Insert into DS_TRAIN_LOGS.TEST
   (UNIT_ID, EVENT_DATE)
 Values
   ('259', TO_DATE('05/28/2015 10:59:13', 'MM/DD/YYYY HH24:MI:SS'));
Insert into DS_TRAIN_LOGS.TEST
   (UNIT_ID, EVENT_DATE)
 Values
   ('259', TO_DATE('06/02/2015 13:17:29', 'MM/DD/YYYY HH24:MI:SS'));
Insert into DS_TRAIN_LOGS.TEST
   (UNIT_ID, EVENT_DATE, DISTANCE)
 Values
   ('259', TO_DATE('06/17/2015 13:28:02', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into DS_TRAIN_LOGS.TEST
   (UNIT_ID, EVENT_DATE)
 Values
   ('259', TO_DATE('06/18/2015 15:54:25', 'MM/DD/YYYY HH24:MI:SS'));
Insert into DS_TRAIN_LOGS.TEST
   (UNIT_ID, EVENT_DATE)
 Values
   ('259', TO_DATE('06/23/2015 17:20:41', 'MM/DD/YYYY HH24:MI:SS'));
Insert into DS_TRAIN_LOGS.TEST
   (UNIT_ID, EVENT_DATE, DISTANCE)
 Values
   ('259', TO_DATE('06/27/2015 01:05:06', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into DS_TRAIN_LOGS.TEST
   (UNIT_ID, EVENT_DATE, DISTANCE)
 Values
   ('259', TO_DATE('06/27/2015 01:05:21', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into DS_TRAIN_LOGS.TEST
   (UNIT_ID, EVENT_DATE)
 Values
   ('259', TO_DATE('07/13/2015 12:46:08', 'MM/DD/YYYY HH24:MI:SS'));
Insert into DS_TRAIN_LOGS.TEST
   (UNIT_ID, EVENT_DATE)
 Values
   ('259', TO_DATE('07/13/2015 17:45:36', 'MM/DD/YYYY HH24:MI:SS'));

Report message to a moderator