Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    1

    Unanswered: Query replace null values with the previous not null value

    Hi all! I have a db oracle with a table as this:

    DATE PRICE
    1/3/2011 1,234
    4/5/2011 1,344
    11/5/2011 -
    13/7/2011 2,569
    23/9/2011 3,865
    24/9/2011 -
    3/10/2011 -
    16/11/2011 4,568

    I want to do a query to replace the null values with the previous not null value obtaining this result:

    DATE PRICE
    1/3/2011 1,234
    4/5/2011 1,344
    11/5/2011 1,344
    13/7/2011 2,569
    23/9/2011 3,865
    24/9/2011 3,865
    3/10/2011 3,865
    16/11/2011 4,568

    Someone can help me? Thanks!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You may want to use LAG with IGNORE NULLS.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    What is your order sequence ?
    What happens if your first value is null in your order sequence ?

    I would start off with using MAX analytic function.

  4. #4
    Join Date
    Jun 2012
    Posts
    13
    Can someone pls tell, if below solution can work?


    DECLARE
    v_temp NUMBER;
    v_upper := SELECT MAX(ROWNUM) FROM /*your table name*/ table_name;
    BEGIN
    SELECT FOR i IN 2..v_upper LOOP
    IF sell IS NULL
    THEN v_temp := SELECT MAX(sell) FROM /*your table name*/ table_name WHERE ROWNUM < i
    --load data in some table or flat file(append)
    ELSE
    --load data in some table or flat file(append)
    END LOOP;
    END;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •