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
;
|
|
|
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 |
cookiemonster
Messages: 13923 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 |
cookiemonster
Messages: 13923 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 #678032 is a reply to message #678025] |
Fri, 01 November 2019 05:04 |
cookiemonster
Messages: 13923 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
lott42_gmail wrote on Thu, 31 October 2019 16:42Thanks, 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.
|
|
|
Goto Forum:
Current Time: Tue May 21 10:38:28 CDT 2024
|