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  |
 |
lott42_gmail
Messages: 146
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
;
|
|
|
|
Current Time: Thu Apr 18 19:35:36 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
