oracle query repeat last known value in data set for all null values

Question

I am trying to create a query that will populate all null values in a dataset with the last known value (123 in this case).

datetime          value
01/SEP/18 00:30   111
01/SEP/18 00:35   122
01/SEP/18 00:40   101
01/SEP/18 00:45   123
01/SEP/18 00:50   NULL
01/SEP/18 00:55   NULL
01/SEP/18 13:00   NULL
...
...
...
01/SEP/18 23:55   NULL

The end result table should look like this

datetime          value
01/SEP/18 00:30   111
01/SEP/18 00:35   122
01/SEP/18 00:40   101
01/SEP/18 00:45   123
01/SEP/18 00:50   123
01/SEP/18 00:55   123
01/SEP/18 13:00   123
...
...
...
01/SEP/18 23:55   123

You should remove your answer from the question and add it as a separate answer, so that you can mark that as the accepted answer. That way, your question is more likely to help future StackOverflow users when they come across your question. — Boneist
– Boneist, Commented Sep 27, 2018 at 8:18

Tim Biegeleisen · Accepted Answer · 2018-09-27 04:27:55Z

1

You could use an update with a correlated subquery:

UPDATE yourTable t1
SET value = (SELECT value
             FROM   (SELECT value, ROWNUM AS rn
                     FROM yourTable t2
                     WHERE t2.datetime < t1.datetime AND t2.value IS NOT NULL
                     ORDER BY datetime DESC)
             WHERE  rn = 1)
WHERE value IS NULL;

The idea behind this update is to find, using a subquery, the most recent value which is not NULL, which is nearest to a given missing value.

If you instead wanted to just select your current table to generate the expected output, you could try:

SELECT
    datetime,
    CASE WHEN value IS NULL
         THEN (SELECT value
               FROM   (SELECT value, ROWNUM AS rn
                       FROM yourTable t2
                       WHERE t2.datetime < t1.datetime AND t2.value IS NOT NULL
                       ORDER BY datetime DESC)
               WHERE  rn = 1)
         ELSE value END AS value
FROM yourTable t1;

edited Sep 27, 2018 at 4:27

answered Sep 27, 2018 at 3:11

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

noobie Over a year ago

Thanks for you response. I probably was not clear enough with the question. But your response has set me on the right path. I have updated my initial post with the query I propose to use. One of the limitations is that I cannot do any updates.

Tim Biegeleisen Over a year ago

You can convert my update into a select. Didn't know about LAST_VALUE; that seems like maybe a better option.

noobie · Accepted Answer · 2018-09-27 08:30:56Z

0

I managed to get the desired result with the following query:

  SELECT DATETIME, VALUE, last_value(VALUE) ignore nulls over (order by DATETIME) 
  FROM
  TABLE1;

answered Sep 27, 2018 at 8:30

noobie

2,6175 gold badges46 silver badges75 bronze badges

Collectives™ on Stack Overflow

oracle query repeat last known value in data set for all null values

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related