CODEWITHSUNDEEP
sqloracle-databaseoracle11ganalytic-functions
brawn
brawn

Reputation: 21

Oracle Analytical Function, data from last but 1 row

I am seeing that, in one of our tables, the row with latest timestamp has NULLs in most columns, except for the last row which has correct values. Is there a way to carry forward those values from last but one row to the last row?

I've been trying to find a solution with a combination of CASE logic/analytical functions but can't figure it out. Any thoughts are appreciated.

Current Output:

with t as    
( 
 select  '99WC2003' as pol_id, to_date( '1/23/2017','mm/dd/yyyy') as Eff_dt , NULL as industry , '-1' as Model from dual Union all
 select  '99WC2003' as pol_id, to_date('1/22/2017','mm/dd/yyyy') as Eff_dt , 'retail' as industry , '0.34' as Model from dual Union all    
 select  '99WC2003' as pol_id, to_date('1/21/2017','mm/dd/yyyy') as Eff_dt , 'retail' as industry , '0.55' as Model from dual Union all    
 select  '10XYZ02'  as pol_id, to_date('1/12/2019','mm/dd/yyyy') as Eff_dt , NULL as industry , '0'  as Model from dual Union all    
 select  '10XYZ02'  as pol_id, to_date('1/11/2019','mm/dd/yyyy') as Eff_dt , 'tech' as industry , '0.3' as Model from dual
)    
 select pol_id,  Eff_dt, industry, model from t

Expected Output:

with t as    
( 
 select  '99WC2003' as pol_id,  to_date('1/23/2017','mm/dd/yyyy') as Eff_dt , 'retail' as industry , '0.34' as Model from dual Union all    
 select  '99WC2003' as pol_id,  to_date('1/22/2017','mm/dd/yyyy') as Eff_dt , 'retail' as industry , '0.34' as Model from dual Union all    
 select  '99WC2003' as pol_id,  to_date('1/21/2017','mm/dd/yyyy') as Eff_dt , 'retail' as industry , '0.55' as Model from dual Union all    
 select  '10XYZ02'  as pol_id,  to_date('1/12/2019','mm/dd/yyyy') as Eff_dt , 'tech' as industry , '0.3' as Model from dual Union all    
 select  '10XYZ02'  as pol_id,  to_date('1/11/2019','mm/dd/yyyy') as Eff_dt ,'tech' as industry , '0.3' as Model from dual
)    
select pol_id,  Eff_dt, industry, model from t

Upvotes: 2

Views: 233

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

You can use lead() analytic function :

select pol_id, Eff_dt, 
       nvl(industry,
           lead(industry) over (order by pol_id desc,Eff_dt desc)) as industry,
       case when nvl(model,0)<=0 then 
            lead(model) over (order by pol_id desc,Eff_dt desc)
       else model end as model
  from t;

Demo

Upvotes: 1

Related Questions