-1

How would it be possible to replace the null value from Order - column with the above existing value that has the the same Document-number?

Document    Value   Value2  Order
1234567       4        7    45678
1234567       2        0    Null
1
  • In general - impossible because of potential ambiguity.
    – Akina
    Sep 28 '20 at 16:50
0

If you know which row represents the "above" or "previous" row, you could use LAG analytic function along with NVL. Something like this:

SQL> with test (document, value, value2, corder) as
  2    (select 1234567, 4, 7, 45678 from dual union all
  3     select 1234567, 2, 0, null  from dual
  4    )
  5  select document, value, value2,
  6    -- LAG ... - previous value per document, sorted by VALUE column in descending order
  7    -- If CORDER is null, take its previous value. How? Using NVL
  8    nvl(corder, lag(corder) over (partition by document order by value desc)) corder
  9  from test
 10  order by document, value desc;

  DOCUMENT      VALUE     VALUE2     CORDER
---------- ---------- ---------- ----------
   1234567          4          7      45678
   1234567          2          0      45678

SQL>
1
  • Thank you for you effort that seems to work. I did actually found solution to my problem by using this: coalesce(order, MAX(order) OVER (partition by document) .
    – Meettu
    Sep 29 '20 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.