Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Need to filling null values with values before the date is weekend/holidays

792365Nov 3 2010 — edited Nov 4 2010
I have a table with Date column, Type column and Rate column.

The problem is when weekends and holidays, Type column and Rate column are null.

I need to filling every null value with Type and Rate values before the date is weekend or holidays.

Example:

I have:

DATE TYPE RATE
01/07/2010 4510PM 3.71
01/07/2010 CETE28 4.59
01/07/2010 TIIE28 4.95
02/07/2010 4510PM 3.82
02/07/2010 CETE28 4.63
02/07/2010 TIIE28 5.11
*03/07/2010 NULL NULL*
*04/07/2010 NULL NULL*
05/07/2010 4510PM 3.91
05/07/2010 CETE28 4.74
05/07/2010 TIIE28 5.25

Will be:

DATE TYPE RATE
01/07/2010 4510PM 3.71
01/07/2010 CETE28 4.59
01/07/2010 TIIE28 4.95
02/07/2010 4510PM 3.82
02/07/2010 CETE28 4.63
02/07/2010 TIIE28 5.11
*03/07/2010 4510PM 3.82*
*03/07/2010 CETE28 4.63*
*03/07/2010 TIIE28 5.11*
*04/07/2010 4510PM 3.82*
*04/07/2010 CETE28 4.63*
*04/07/2010 TIIE28 5.11*
05/07/2010 4510PM 3.91
05/07/2010 CETE28 4.74
05/07/2010 TIIE28 5.25

What I could do?
This post has been answered by Frank Kulash on Nov 3 2010
Jump to Answer

Comments

Frank Kulash
Answer
Hi,

You can use the analytic LAST_VALUE function to get the latest work day on or before each date in your table. This will be the same as the current day for all work days.
The do a self-join, to combine each current row (c) with the latest work day (l): 
WITH	got_last_work_day	AS
(
	SELECT	dt, type, rate
	,	LAST_VALUE ( CASE 
				WHEN  type  IS NOT NULL 
				THEN  dt 
			    END
			    IGNORE NULLS
			   ) OVER (ORDER BY dt)	AS last_work_day
	FROM	table_x
)
SELECT	  c.dt, l.type, l.rate
FROM	  got_last_work_day	c
JOIN	  got_last_work_day	l  ON  	(    c.dt		= l.dt
					AND  c.type		= l.type
					)
				   OR	(    c.last_work_day	= l.dt
					AND  c.type		IS NULL
					)
ORDER BY  c.dt
,	  l.type
;
Among other things, I assume that type is NULL if (and only if) the row represents a holiday or weekend, and that the combination (dt, type) is uniuqe.
Marked as Answer by 792365 · Sep 27 2020
Aketi Jyuuzou
I like Hierarchical Query B-)
with t(ColDATE,ColTYPE,RATE) as(
select date '2010-07-01','4510PM',3.71 from dual union all
select date '2010-07-01','CETE28',4.59 from dual union all
select date '2010-07-01','TIIE28',4.95 from dual union all
select date '2010-07-02','4510PM',3.82 from dual union all
select date '2010-07-02','CETE28',4.63 from dual union all
select date '2010-07-02','TIIE28',5.11 from dual union all
select date '2010-07-03', NULL   ,NULL from dual union all
select date '2010-07-04', NULL   ,NULL from dual union all
select date '2010-07-05','4510PM',3.91 from dual union all
select date '2010-07-05','CETE28',4.74 from dual union all
select date '2010-07-05','TIIE28',5.25 from dual)
select ColDATE,
connect_by_root ColTYPE as ColTYPE,
connect_by_root RATE as RATE
  from t
start with ColTYPE is not null
connect by prior ColDATE+1=ColDATE
       and ColTYPE is null
order by ColDATE,ColTYPE;

COLDATE   COLTYP  RATE
--------  ------  ----
10-07-01  4510PM  3.71
10-07-01  CETE28  4.59
10-07-01  TIIE28  4.95
10-07-02  4510PM  3.82
10-07-02  CETE28  4.63
10-07-02  TIIE28  5.11
10-07-03  4510PM  3.82
10-07-03  CETE28  4.63
10-07-03  TIIE28  5.11
10-07-04  4510PM  3.82
10-07-04  CETE28  4.63
10-07-04  TIIE28  5.11
10-07-05  4510PM  3.91
10-07-05  CETE28  4.74
10-07-05  TIIE28  5.25
1 person found this helpful
Aketi Jyuuzou
I like recursive with clause B-)
with t(ColDATE,ColTYPE,RATE) as(
select date '2010-07-01','4510PM',3.71 from dual union all
select date '2010-07-01','CETE28',4.59 from dual union all
select date '2010-07-01','TIIE28',4.95 from dual union all
select date '2010-07-02','4510PM',3.82 from dual union all
select date '2010-07-02','CETE28',4.63 from dual union all
select date '2010-07-02','TIIE28',5.11 from dual union all
select date '2010-07-03', NULL   ,NULL from dual union all
select date '2010-07-04', NULL   ,NULL from dual union all
select date '2010-07-05','4510PM',3.91 from dual union all
select date '2010-07-05','CETE28',4.74 from dual union all
select date '2010-07-05','TIIE28',5.25 from dual),
rec(ColDATE,ColTYPE,RATE) as(
select ColDATE,ColTYPE,RATE
  from t
 where ColTYPE is not null
union all
select b.ColDATE,a.ColTYPE,a.RATE
  from rec a,t b
 where a.ColDATE+1=b.ColDATE
   and b.ColTYPE is null)
select*from rec order by ColDATE,ColTYPE;
1 person found this helpful
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 2 2010
Added on Nov 3 2010
#code, #date, #pl-sql, #plsql, #sql, #sql-developer
3 comments
528 views