Oracle PL/SQL Model Clause for Recursion

Question

Looking for an answer on how to fill in NULL values based on values in prior rows when the number of consecutive NULL values varies. It seems like the PL/SQL MODEL clause should be able to do it, but I haven't been able to figure out the syntax at all. Here's a toy data set:

CREATE TABLE SO_EXAMPLE_TMP (
  EXAMPLE_ID VARCHAR2(5)
  , SALES_STAGE VARCHAR(16)
  , RECORD_MONTH DATE
);

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

INSERT ALL
INTO SO_EXAMPLE_TMP VALUES ('ABC12', 'PIPELINE', '01-JAN-2017') 
INTO SO_EXAMPLE_TMP VALUES ('ABC12', NULL      , '01-FEB-2017')  
INTO SO_EXAMPLE_TMP VALUES ('ABC12', NULL      , '01-MAR-2017') 
INTO SO_EXAMPLE_TMP VALUES ('ABC12', NULL      , '01-APR-2017')
INTO SO_EXAMPLE_TMP VALUES ('ABC12', 'COMMIT'  , '01-MAY-2017')
INTO SO_EXAMPLE_TMP VALUES ('ABC12', NULL      , '01-JUN-2017')
INTO SO_EXAMPLE_TMP VALUES ('ABC12', 'CLOSED'  , '01-JUL-2017')
INTO SO_EXAMPLE_TMP VALUES ('XYZ98', 'PIPELINE', '01-FEB-2017')   
INTO SO_EXAMPLE_TMP VALUES ('XYZ98', 'COMMIT'  , '01-MAR-2017') 
INTO SO_EXAMPLE_TMP VALUES ('XYZ98', NULL      , '01-APR-2017') 
INTO SO_EXAMPLE_TMP VALUES ('XYZ98', NULL      , '01-MAY-2017')
INTO SO_EXAMPLE_TMP VALUES ('XYZ98', 'CLOSED'  , '01-JUN-2017')  
SELECT 1 FROM DUAL;

My goal on output is to fill in the NULL values with the value from the prior row, e.g. rows 2-4 in the SALES_STAGE column should all show 'PIPELINE' as the value because they are currently NULL and the closest prior populated value is 'PIPELINE' in row 1. Likewise row 6 should have 'COMMIT' for the SALES_STAGE, etc. If there's an easier way to do this than using MODEL I'm happy to go with that too, just having a really hard time getting recursion to work in the was I want here. Thanks.

maybe this link can help you, stackoverflow.com/questions/26977267/… — eifla001
– eifla001, Commented Jan 31, 2018 at 1:00

pablomatico · Accepted Answer · 2018-01-31 07:38:53Z

3

Using the LAST_VALUE analytic function should do the trick, something like this:

SELECT example_id, LAST_VALUE(sales_stage IGNORE NULLS) OVER (PARTITION BY example_id ORDER BY record_month), record_month
FROM so_example_tmp

answered Jan 31, 2018 at 7:38

pablomatico

2,24221 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom.Rampley Over a year ago

Thanks you very much. I was definitely over complicating it, I wasn't aware of the IGNORE NULLS clause existing but that was a simple fix!

Collectives™ on Stack Overflow

Oracle PL/SQL Model Clause for Recursion

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related