Home » SQL & PL/SQL » SQL & PL/SQL » Update null value columns based upon previous non null columns (11.2)
Update null value columns based upon previous non null columns [message #678018] Thu, 31 October 2019 10:38 Go to next message
lott42_gmail
Messages: 142
Registered: June 2010
Senior Member
I have sets of data based upon an "ID", for each "ID", there can be many records associated with it based on "to_date". I need to possibly fill in null value(s) for 2 attribute columns(x_days, y_days) based upon subsequent last non null values for each "ID" and "to_date".

create table MRADW_STAGE.AVAILS_LO_UPD(AVAIL_ID number, VALID_TO_DATE date, X_DAYS number, y_DAYS number);

insert all
  into MRADW_STAGE.AVAILS_LO_UPD (AVAIL_ID, VALID_TO_DATE, X_DAYS, Y_DAYS) values (1, SYSDATE, 10, null)
  into MRADW_STAGE.AVAILS_LO_UPD (AVAIL_ID, VALID_TO_DATE, X_DAYS, Y_DAYS) values (1, SYSDATE+1, 10, 20)
  into MRADW_STAGE.AVAILS_LO_UPD (AVAIL_ID, VALID_TO_DATE, X_DAYS, Y_DAYS) values (1, SYSDATE+2, null, 10)
  into MRADW_STAGE.AVAILS_LO_UPD (AVAIL_ID, VALID_TO_DATE, X_DAYS, Y_DAYS) values (1, SYSDATE+3, null, null)
  into MRADW_STAGE.AVAILS_LO_UPD (AVAIL_ID, VALID_TO_DATE, X_DAYS, Y_DAYS) values (1, SYSDATE+4, 5, null)
  into MRADW_STAGE.AVAILS_LO_UPD (AVAIL_ID, VALID_TO_DATE, X_DAYS, Y_DAYS) values (1, SYSDATE+5, null, 20)
  into MRADW_STAGE.AVAILS_LO_UPD (AVAIL_ID, VALID_TO_DATE, X_DAYS, Y_DAYS) values (1, SYSDATE+6, 20, null)
  into MRADW_STAGE.AVAILS_LO_UPD (AVAIL_ID, VALID_TO_DATE, X_DAYS, Y_DAYS) values (2, SYSDATE, 10, 10)
  into MRADW_STAGE.AVAILS_LO_UPD (AVAIL_ID, VALID_TO_DATE, X_DAYS, Y_DAYS) values (2, SYSDATE+1, null, null)
  into MRADW_STAGE.AVAILS_LO_UPD (AVAIL_ID, VALID_TO_DATE, X_DAYS, Y_DAYS) values (2, SYSDATE+2, null, null)
SELECT * FROM dual;

Data:
AVAIL_ID VALID_TO_DATE  X_DAYS  Y_DAYS
1	31-OCT-19	10	
1	01-NOV-19	10	20
1	02-NOV-19		10
1	03-NOV-19		
1	04-NOV-19	5	
1	05-NOV-19		20
1	06-NOV-19	20	
2	31-OCT-19	10	10
2	01-NOV-19		
2	02-NOV-19		

I want the output to look like this
AVAIL_ID VALID_TO_DATE  X_DAYS  Y_DAYS
1	31-OCT-19	10	
1	01-NOV-19	10	20
1	02-NOV-19	10	10
1	03-NOV-19	10	10
1	04-NOV-19	5	10
1	05-NOV-19	5	20
1	06-NOV-19	20	20
2	31-OCT-19	10	10
2	01-NOV-19	10	10
2	02-NOV-19	10	10

using "X_DAYS"

I need to fill in
Id 1 02-NOV-19 with 10
Id 1 03-NOV-19 with 10

Because,
Id:1 01-NOV-19 x_days=10 was last non null value I saw

I need to fill in
Id 1 05-NOV-19 with 5

Because,
Id:1 04-NOV-19 x_days=5 was last non null value I saw

I need to fill in
Id 2 01-NOV-19 with 10
Id 2 02-NOV-19 with 10

Because,
Id:2 31-OCT-19 x_days=10 was last non null value I saw

I wrote a quick pl/sql procedure to do this:

CREATE OR REPLACE PROCEDURE avail_info_upd11 (
    p_avail_id IN NUMBER DEFAULT NULL
) AS

    CURSOR c_avail_info (
        cp_avail_id IN NUMBER
    ) IS SELECT
        ROWID,
        avail_id,
        valid_to_date,
        x_days,
        y_days
         FROM
        mradw_stage.avails_lo_upd a
         WHERE
        1 = 1
        --and a.avail_id = cp_avail_id
    ORDER BY
        avail_id,
        valid_to_date;

    r_avail_info            c_avail_info%rowtype; 

  --Error Exceptions 
    e_generic_error EXCEPTION;
    e_others_error EXCEPTION; 

  --Error Vars 
    v_error                 NUMBER;
    v_error_code            NUMBER;
    v_error_location        VARCHAR2(4000);
    v_error_msg             VARCHAR2(4000);
    v_error_msg_func_proc   VARCHAR2(4000); 

  --Generic Pack/Proc Vars 

  --Generic Results/Message Vars 
    v_result                NUMBER := 0;
    v_result_msg            VARCHAR2(4000);
    v_count                 NUMBER := 0;
    v_update_cnt            NUMBER := 0;
    v_x_days                NUMBER; -- temp
    v_y_days                NUMBER; --temp 
    v_update_avail          BOOLEAN := false;
    v_date                  DATE;

  --Parm Vars 
    v_avail_id              VARCHAR2(100) := '0';

    FUNCTION update_avail_id (
        cp_avail_id   IN NUMBER DEFAULT NULL,
        cp_date       IN DATE DEFAULT NULL,
        cp_x_value    IN NUMBER DEFAULT NULL,
        cp_y_value    IN NUMBER DEFAULT NULL,
        cp_rowid      IN VARCHAR2 DEFAULT NULL
    ) RETURN NUMBER
      --UPDATE_AVAIL_ID(CP_rowid in varchar2 default null) return number
        IS
    BEGIN
        v_update_cnt := v_update_cnt + 1;
        dbms_output.put_line('begin update avail id: '
        || cp_avail_id
        || ' date: '
        || TO_CHAR(cp_date,'mm/dd/yyyy hh:mi am')
        || ' rowid: '
        || cp_rowid
        || ' x_days: '
        || cp_x_value
        || ' '
        || TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss AM') );

        UPDATE mradw_stage.avails_lo_upd
            SET
                x_days = cp_x_value,
                y_days = cp_y_value
        WHERE
            1 = 1
            AND   ROWID = cp_rowid;

        RETURN '';
    EXCEPTION
        WHEN OTHERS THEN
            RETURN sqlerrm;
    END;

BEGIN
    dbms_output.put_line(' starting. ');
    FOR r_avail_info IN c_avail_info(cp_avail_id => '') LOOP
        v_count := v_count + 1;
        IF
            nvl(v_avail_id,0) <> r_avail_info.avail_id
        THEN  --new avail, when compared to old one.
            v_x_days := NULL;
            v_y_days := NULL;
        END IF;

        IF
            v_x_days IS NOT NULL
        THEN
            IF
                r_avail_info.x_days IS NULL
            THEN
                v_update_avail := true;
            ELSIF r_avail_info.x_days <> v_x_days THEN
                v_x_days := r_avail_info.x_days;
                v_update_avail := true;
            END IF;
        ELSE
            v_x_days := r_avail_info.x_days;
        END IF;

        IF
            v_y_days IS NOT NULL
        THEN
            IF
                r_avail_info.y_days IS NULL
            THEN
                v_update_avail := true;
            ELSIF r_avail_info.y_days <> v_y_days THEN
                v_y_days := r_avail_info.y_days;
                v_update_avail := true;
            END IF;
        ELSE
            v_y_days := r_avail_info.y_days;
        END IF;

        IF
            ( v_update_avail = true )
        THEN   --Try to update avail table 
            v_result_msg := update_avail_id(cp_avail_id => r_avail_info.avail_id,cp_date => r_avail_info.valid_to_date,cp_x_value => v_x_days,cp_y_value
=> v_y_days,cp_rowid => r_avail_info.rowid);

            IF
                ( v_result_msg IS NOT NULL )
            THEN
                v_error_msg := v_result_msg;
                RAISE e_generic_error;
            END IF;

            v_update_avail := false;
        END IF;

        v_avail_id := r_avail_info.avail_id;
    END LOOP;

    COMMIT;
    dbms_output.put_line(v_update_cnt
    || ' out of '
    || v_count
    || ' rows updated'
    || ' as of '
    || TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss AM') );

    return;
EXCEPTION
    WHEN e_generic_error THEN
        ROLLBACK;
        dbms_output.put_line(v_error_msg);
        return;
    WHEN OTHERS THEN
        ROLLBACK;
        v_error_location := '"ERROR(Main - ''prc_get_casrep_notes). ''"';
        dbms_output.put_line(v_error_msg);
        return;
END avail_info_upd11;


Can this be done with pure sql(it doesn't quite do what I want) in a recursive lag/lead functionality such as:

SELECT
    avail_id,
    valid_to_date,
    X_DAYS,
    CASE
            WHEN x_days IS NULL THEN LAG(x_days) OVER(
                PARTITION BY avail_id
                ORDER BY
                    valid_to_date
            )
            ELSE x_days
        end
    X_DAYS_LAG,
    Y_DAYS,
    CASE
            WHEN y_days IS NULL THEN LAG(y_days) OVER(
                PARTITION BY avail_id
                ORDER BY
                    valid_to_date
            )
            ELSE y_days
        END
    y_days_lag
from
    MRADW_STAGE.AVAILS_LO_UPD
order by avail_id, valid_to_date
    ;


Re: Update null value columns based upon previous non null columns [message #678020 is a reply to message #678018] Thu, 31 October 2019 11:00 Go to previous messageGo to next message
cookiemonster
Messages: 13755
Registered: September 2008
Location: Rainy Manchester
Senior Member
First off - leave out schema names in your test script - we don't have your users.

CASE WHEN col IS NULL THEN .... ELSE col END
is a long winded way of writing
nvl(col, ...)

The analytic last_value with ignore nulls will get you what you need:
SQL> SELECT avail_id,
  2         valid_to_date,
  3         nvl(X_DAYS, last_value(x_days IGNORE NULLS)
  4                     OVER(PARTITION BY avail_id
  5                          ORDER BY valid_to_date
  6                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS x_days,
  7         nvl(Y_DAYS, last_value(y_days IGNORE NULLS)
  8                     OVER(PARTITION BY avail_id
  9                          ORDER BY valid_to_date
 10                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS y_days_lag
 11  FROM AVAILS_LO_UPD
 12  ORDER by avail_id, valid_to_date;

  AVAIL_ID VALID_TO_DATE     X_DAYS Y_DAYS_LAG
---------- ------------- ---------- ----------
         1 31/10/2019 15         10 
         1 01/11/2019 15         10         20
         1 02/11/2019 15         10         10
         1 03/11/2019 15         10         10
         1 04/11/2019 15          5         10
         1 05/11/2019 15          5         20
         1 06/11/2019 15         20         20
         2 31/10/2019 15         10         10
         2 01/11/2019 15         10         10
         2 02/11/2019 15         10         10

10 rows selected
You can use that in a merge using rowid to match:
MERGE INTO AVAILS_LO_UPD d
USING (
SELECT ROWID r, 
       valid_to_date,
       nvl(X_DAYS, last_value(x_days IGNORE NULLS) 
                   OVER(PARTITION BY avail_id 
                        ORDER BY valid_to_date 
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS x_days,
       nvl(Y_DAYS, last_value(y_days IGNORE NULLS) 
                   OVER(PARTITION BY avail_id 
                        ORDER BY valid_to_date 
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS y_days
FROM AVAILS_LO_UPD
) s
ON (d.rowid = s.r)
WHEN MATCHED THEN
    UPDATE SET d.x_days = s.x_days,
               d.y_days = s.y_days;
Re: Update null value columns based upon previous non null columns [message #678021 is a reply to message #678020] Thu, 31 October 2019 11:19 Go to previous messageGo to next message
cookiemonster
Messages: 13755
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your procedure is way more complicated than it needs to be. This does the same job:
DECLARE

  l_xdays avails_lo_upd.x_days%TYPE;
  l_ydays avails_lo_upd.x_days%TYPE;
  l_avail_id avails_lo_upd.avail_id%TYPE;
  l_count NUMBER := 0;
  l_count_udpated NUMBER := 0;
  
BEGIN
  
  FOR rec IN (SELECT avail_id, valid_to_Date, ROWID, x_days, y_days
              FROM avails_lo_upd 
              ORDER BY avail_id, valid_to_date) LOOP
    
    l_count := l_count + 1;
    
    IF l_avail_id IS NULL OR rec.avail_id != l_avail_id THEN
      --new id
      l_avail_id := rec.avail_id;
      l_xdays := rec.x_days;
      l_ydays := rec.y_days;
      
    ELSE
      
      --current id
      IF rec.x_days IS NOT NULL THEN
        l_xdays := rec.x_days;
      END IF;
      
      IF rec.y_days IS NOT NULL THEN
        l_ydays := rec.y_days;
      END IF;
      
      IF rec.x_days IS NULL OR rec.y_days IS NULL THEN
        
        l_count_udpated := l_count_udpated + 1;
      
        UPDATE avails_lo_upd
        SET x_days = l_xdays,
            y_days = l_ydays
        WHERE ROWID = rec.rowid;
        
      END IF;
      
    END IF;
    
  END LOOP;
  
  dbms_output.put_line(l_count_udpated
    || ' out of '
    || l_count
    || ' rows updated'
    || ' as of '
    || TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss AM') );
    
END;

Also that's a really bad approach to error handling. If you aren't going to do anything useful with an exception, and you're not, you should just let it propagate.
You should never, ever use dbms_output to report error messages.
If you take the exception handler out sqlplus will always report the full error stack trace. If you leave it in you'll only see an error if you are running in sqlplus with serveroutput on or in some other tool that actually reports dbms_output.
Re: Update null value columns based upon previous non null columns [message #678022 is a reply to message #678021] Thu, 31 October 2019 11:29 Go to previous messageGo to next message
cookiemonster
Messages: 13755
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also on the subject of exception handling:
Your exception handler in the function will cause an exception - you're doing RETURN sqlerrm but the return type is number.
But then you shouldn't ever return sqlerrm, as mentioned above - let it propagate. Life is so much simpler if you do.
Re: Update null value columns based upon previous non null columns [message #678024 is a reply to message #678022] Thu, 31 October 2019 11:42 Go to previous messageGo to next message
Michel Cadot
Messages: 66720
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... Also read WHEN OTHERS.

[Updated on: Thu, 31 October 2019 11:42]

Report message to a moderator

Re: Update null value columns based upon previous non null columns [message #678025 is a reply to message #678022] Thu, 31 October 2019 11:42 Go to previous messageGo to next message
lott42_gmail
Messages: 142
Registered: June 2010
Senior Member
Thanks, cookiemonster

Yes, I need to get better at my error handling. Also, my logic probably wasn't the best but it did seem to get me what I wanted. I'll blame stress at trying to get this done by Wednesday and me not being the sharpest knife in the drawer...thanks for spending the extra time looking at my procedure Razz

Thanks for the sql logic. That, I could not figure out at all...
Re: Update null value columns based upon previous non null columns [message #678026 is a reply to message #678025] Thu, 31 October 2019 11:42 Go to previous messageGo to next message
Michel Cadot
Messages: 66720
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Read WHEN OTHERS.

Re: Update null value columns based upon previous non null columns [message #678027 is a reply to message #678026] Thu, 31 October 2019 11:56 Go to previous messageGo to next message
lott42_gmail
Messages: 142
Registered: June 2010
Senior Member
Michel Cadot wrote on Thu, 31 October 2019 11:42

Read WHEN OTHERS.

OK, Thanks Michel
Re: Update null value columns based upon previous non null columns [message #678032 is a reply to message #678025] Fri, 01 November 2019 05:04 Go to previous message
cookiemonster
Messages: 13755
Registered: September 2008
Location: Rainy Manchester
Senior Member
lott42_gmail wrote on Thu, 31 October 2019 16:42
Thanks, cookiemonster

Yes, I need to get better at my error handling.
Just to be completely clear - there should be no exception handling in that what so ever unless you really want to log errors in a table. And if you want to do that you should call a logging procedure that's an autonomous_transaction and use raise afterwards.

In my rather large app about 99% of the handlers are no_data_found, the rest are mainly dup_val_on_index and too_many_rows and there's a single WHEN OTHERS - that's used in a message processing package to write the failed message to a log table with the full error stack before deleting it from the queue so the next message can be processed.
Previous Topic: ANALYZE TABLE STATEMENT
Next Topic: ALTER TABLE STATEMENT FOR CLUSTER
Goto Forum:
  


Current Time: Tue Dec 10 00:40:01 CST 2019