Compute the child record value based on the parent value in Oracle PLSQL

Question

I am trying to formulate a query in Oracle DB such that it computes the start_date value for the rows having it as null based on the numoddays , lvl (level), and the previous level's start_date column.

For an example: Linenumber 3 and item 123:

Start_date = Start_date of previous level (2) + numofdays of current row

i.e Start_date = 03-FEB-23 01:54:00 PM + 1 = 04-FEB-23 01:54:00 PM

Notice that the non-null start date can be any arbitrary date and we have to compute the subsequent null rows for that item and the trailing non-null start_date wont follow the same pattern

ie Start_date of line number 2 is 03-FEB-23 01:54:00 PM which is not equal to 24-JAN-23 01:54:00 PM + 2 (from line number 2)

Sample table code:

  select 1 LineNumber, 123 item, 1 lvl, 2 numofdays, sysdate start_date from dual
  union all
  select 2 , 123 , 2, 2, sysdate + 10 from dual
  union all
  select 3 , 123 , 3, 1, null from dual
  union all
  select 4 , 123 , 4, 3, null from dual
  union all
  select 5 , 123 , 5, 2, null from dual
  union all
  select 6 , 345 , 1, 1, sysdate+2 from dual
  union all
  select 7 , 345 , 2, 2, null from dual
  union all
  select 8 , 345 , 3, 1, null from dual

Desired Result:

  select 1 LineNumber, 123 item, 1 lvl, 2 numofdays, sysdate start_date from dual
  union all
  select 2 , 123 , 2, 2, sysdate + 10 from dual
  union all
  select 3 , 123 , 3, 1, sysdate +10 +1 from dual
  union all
  select 4 , 123 , 4, 3, sysdate +10 +1+3 from dual
  union all
  select 5 , 123 , 5, 2, sysdate +10 +3+1+2 from dual
  union all
  select 6 , 345 , 1, 1, sysdate+2 from dual
  union all
  select 7 , 345 , 2, 2, sysdate +2 +2 from dual
  union all
  select 8 , 345 , 3, 1, sysdate +2 +2+1 from dual

Any help would be greatly appreciated

The normal method is with a LAG or LAST_VALUE windowing function. But as you have multiple consecutive rows whose numofdays would need to be added to the result of a previous calculation, it would get quite complex to do that in SQL. But it would be super easy in PL/SQL. Just pull in the cursor ordered by linenumber and keep a variable that keeps the last start_date and adds up num_days for null rows and computes the new start_date, until a non-null start_date is found which then resets the num_days counter. — Paul W, Jan 24 '23 at 16:14
@PaulW Using MODEL clause makes it easy to deal with those consecutive nulls in start date column... — d r, Jan 24 '23 at 19:46

score 1 · Answer 1 · answered Jan 24 '23 at 19:27

This is an ideal case for using MODEL clause. Your instruction to "... compute the start_date value for the rows having it as null based on the numoddays , lvl (level), and the previous level's start_date column." could be modeled just like that:

Select    LINE_NUM, ITEM, LVL, NUM_OF_DAYS, START_DATE
From      tbl 
MODEL     Partition By  (ITEM)
          Dimension By  (LVL)
          Measures      (LINE_NUM, NUM_OF_DAYS, START_DATE)
          Rules         ( START_DATE[ANY] = CASE WHEN START_DATE[CV()] Is Not Null 
                                                 THEN START_DATE[CV()] 
                                            ELSE START_DATE[CV() -1 ] + NUM_OF_DAYS[CV()] END  )

In this case modeling is partitioned by ITEM column saying that for ANY (Dimension) LVL the START_DATE which Is Not Null stays as it is in that LVL (CV() - Current Value of LVL) and ELSE when START_DATE Is Null then take the date from previous LVL ( CV()-1 ) and add NUM_OF_DAYS from current LVL.
With Your sample data:

WITH
    tbl (LINE_NUM, ITEM, LVL, NUM_OF_DAYS, START_DATE) AS
        (
            Select 1, 123 , 1, 2, SYSDATE       From Dual Union All
            Select 2, 123 , 2, 2, SYSDATE + 10  From Dual Union All
            Select 3, 123 , 3, 1, null          From Dual Union All
            Select 4, 123 , 4, 3, null          From Dual Union All
            Select 5, 123 , 5, 2, null          From Dual Union All
            Select 6, 345 , 1, 1, SYSDATE+2     From Dual Union All
            Select 7, 345 , 2, 2, null          From Dual Union All
            Select 8, 345 , 3, 1, null          From Dual
        )

... the result would be:

  LINE_NUM       ITEM        LVL NUM_OF_DAYS START_DATE
---------- ---------- ---------- ----------- ----------
         1        123          1           2 24-JAN-23  
         2        123          2           2 03-FEB-23  
         3        123          3           1 04-FEB-23  
         4        123          4           3 07-FEB-23  
         5        123          5           2 09-FEB-23  
         6        345          1           1 26-JAN-23  
         7        345          2           2 28-JAN-23  
         8        345          3           1 29-JAN-23

score 0 · Answer 2 · answered Jan 24 '23 at 18:26

From Oracle 12, you can use MATCH_RECOGNIZE for row-by-row pattern matching:

SELECT LineNumber,
       item,
       lvl,
       numofdays,
       first_start_date + COALESCE(total_days, 0) AS start_date
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY item
  ORDER BY lvl
  MEASURES
    FIRST(start_date) AS first_start_date,
    SUM(null_start_date.numofdays) AS total_days
  ALL ROWS PER MATCH
  PATTERN (not_null_start_date null_start_date*)
  DEFINE
    not_null_start_date AS start_date IS NOT NULL,
    null_start_date     AS start_date IS NULL
)

Which, for the sample data:

CREATE TABLE table_name (LineNumber, item, lvl, numofdays, start_date) AS
  select 1, 123, 1, 2, sysdate from dual union all
  select 2, 123, 2, 2, sysdate + 10 from dual union all
  select 3, 123, 3, 1, null from dual union all
  select 4, 123, 4, 3, null from dual union all
  select 5, 123, 5, 2, null from dual union all
  select 6, 345, 1, 1, sysdate+2 from dual union all
  select 7, 345, 2, 2, null from dual union all
  select 8, 345, 3, 1, null from dual;

Outputs:

LINENUMBER	ITEM	LVL	NUMOFDAYS	START_DATE
1	123	1	2	2023-01-24 18:23:54
2	123	2	2	2023-02-03 18:23:54
3	123	3	1	2023-02-04 18:23:54
4	123	4	3	2023-02-07 18:23:54
5	123	5	2	2023-02-09 18:23:54
6	345	1	1	2023-01-26 18:23:54
7	345	2	2	2023-01-28 18:23:54
8	345	3	1	2023-01-29 18:23:54

fiddle

Compute the child record value based on the parent value in Oracle PLSQL

2 Answers2