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

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

CodePudding user response：

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

CodePudding user response：

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