Avoid ZERO and NULL Record

Question

I have shared my requirement below:

I have a table test_forward having two columns

table creation script:

CREATE TABLE TEST_FORWARD
(
  CUST_DATE   DATE,
  CUST_VALUE  NUMBER
);

I have data in that table as well:

Insert into TEST_FORWARD
   (CUST_DATE, CUST_VALUE)
 Values
   (TO_DATE('6/1/2024', 'MM/DD/YYYY'), 0);
Insert into TEST_FORWARD
   (CUST_DATE, CUST_VALUE)
 Values
   (TO_DATE('6/2/2024', 'MM/DD/YYYY'), 0);
Insert into TEST_FORWARD
   (CUST_DATE, CUST_VALUE)
 Values
   (TO_DATE('6/3/2024', 'MM/DD/YYYY'), 20000);
Insert into TEST_FORWARD
   (CUST_DATE, CUST_VALUE)
 Values
   (TO_DATE('6/4/2024', 'MM/DD/YYYY'), 20000);
Insert into TEST_FORWARD
   (CUST_DATE, CUST_VALUE)
 Values
   (TO_DATE('6/5/2024', 'MM/DD/YYYY'), 20000);
Insert into TEST_FORWARD
   (CUST_DATE, CUST_VALUE)
 Values
   (TO_DATE('6/6/2024', 'MM/DD/YYYY'), 20000);
Insert into TEST_FORWARD
   (CUST_DATE, CUST_VALUE)
 Values
   (TO_DATE('6/7/2024', 'MM/DD/YYYY'), 20000);
Insert into TEST_FORWARD
   (CUST_DATE, CUST_VALUE)
 Values
   (TO_DATE('6/8/2024', 'MM/DD/YYYY'), 0);
Insert into TEST_FORWARD
   (CUST_DATE, CUST_VALUE)
 Values
   (TO_DATE('6/9/2024', 'MM/DD/YYYY'), 0);
Insert into TEST_FORWARD
   (CUST_DATE, CUST_VALUE)
 Values
   (TO_DATE('6/10/2024', 'MM/DD/YYYY'), 20000);
Insert into TEST_FORWARD
   (CUST_DATE, CUST_VALUE)
 Values
   (TO_DATE('6/11/2024', 'MM/DD/YYYY'), 20000);
COMMIT;

Now I need, if my CUST_VALUE is 0/null then in the third column it should show next non zero/null cust_date. For example I have created one new table where I will insert the data along with that third column

Second Table Script:

CREATE TABLE SCPOMGR.TEST_FORWARD_MODIFIED
(
  CUST_DATE      DATE,
  CUST_VALUE     NUMBER,
  CUST_MOD_DATE  DATE
);

Data will be look like below:

Insert into TEST_FORWARD_MODIFIED
   (CUST_DATE, CUST_VALUE, CUST_MOD_DATE)
 Values
   (TO_DATE('6/1/2024', 'MM/DD/YYYY'), 0, TO_DATE('6/3/2024', 'MM/DD/YYYY'));
Insert into TEST_FORWARD_MODIFIED
   (CUST_DATE, CUST_VALUE, CUST_MOD_DATE)
 Values
   (TO_DATE('6/2/2024', 'MM/DD/YYYY'), 0, TO_DATE('6/3/2024', 'MM/DD/YYYY'));
Insert into TEST_FORWARD_MODIFIED
   (CUST_DATE, CUST_VALUE, CUST_MOD_DATE)
 Values
   (TO_DATE('6/3/2024', 'MM/DD/YYYY'), 20000, TO_DATE('6/3/2024', 'MM/DD/YYYY'));
Insert into TEST_FORWARD_MODIFIED
   (CUST_DATE, CUST_VALUE, CUST_MOD_DATE)
 Values
   (TO_DATE('6/4/2024', 'MM/DD/YYYY'), 20000, TO_DATE('6/4/2024', 'MM/DD/YYYY'));
Insert into TEST_FORWARD_MODIFIED
   (CUST_DATE, CUST_VALUE, CUST_MOD_DATE)
 Values
   (TO_DATE('6/5/2024', 'MM/DD/YYYY'), 20000, TO_DATE('6/5/2024', 'MM/DD/YYYY'));
Insert into TEST_FORWARD_MODIFIED
   (CUST_DATE, CUST_VALUE, CUST_MOD_DATE)
 Values
   (TO_DATE('6/6/2024', 'MM/DD/YYYY'), 20000, TO_DATE('6/6/2024', 'MM/DD/YYYY'));
Insert into TEST_FORWARD_MODIFIED
   (CUST_DATE, CUST_VALUE, CUST_MOD_DATE)
 Values
   (TO_DATE('6/7/2024', 'MM/DD/YYYY'), 20000, TO_DATE('6/7/2024', 'MM/DD/YYYY'));
Insert into TEST_FORWARD_MODIFIED
   (CUST_DATE, CUST_VALUE, CUST_MOD_DATE)
 Values
   (TO_DATE('6/8/2024', 'MM/DD/YYYY'), 0, TO_DATE('6/10/2024', 'MM/DD/YYYY'));
Insert into TEST_FORWARD_MODIFIED
   (CUST_DATE, CUST_VALUE, CUST_MOD_DATE)
 Values
   (TO_DATE('6/9/2024', 'MM/DD/YYYY'), 0, TO_DATE('6/10/2024', 'MM/DD/YYYY'));
Insert into TEST_FORWARD_MODIFIED
   (CUST_DATE, CUST_VALUE, CUST_MOD_DATE)
 Values
   (TO_DATE('6/10/2024', 'MM/DD/YYYY'), 20000, TO_DATE('6/10/2024', 'MM/DD/YYYY'));
Insert into TEST_FORWARD_MODIFIED
   (CUST_DATE, CUST_VALUE, CUST_MOD_DATE)
 Values
   (TO_DATE('6/11/2024', 'MM/DD/YYYY'), 20000, TO_DATE('6/11/2024', 'MM/DD/YYYY'));
COMMIT;

Answer 1

You can use the LEAD analytic function combined with CASE expressions to generate your modified data:

INSERT INTO test_forward_modified (CUST_DATE, CUST_VALUE, CUST_MOD_DATE)
SELECT cust_date,
       cust_value,
       CASE
       WHEN cust_value != 0
       THEN cust_date
       ELSE LEAD(
              CASE
              WHEN cust_value != 0
              THEN cust_date
              END
            ) IGNORE NULLS OVER (ORDER BY cust_date)
       END
FROM   test_forward;

or the FIRST_VALUE analytic function:

INSERT INTO test_forward_modified (CUST_DATE, CUST_VALUE, CUST_MOD_DATE)
SELECT cust_date,
       cust_value,
       FIRST_VALUE(
         CASE WHEN cust_value != 0 THEN cust_date END
       ) IGNORE NULLS OVER (
         ORDER BY cust_date
         ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
       )
FROM   test_forward;

Then, given your sample data, after either INSERT the table will contain:

CUST_DATE	CUST_VALUE	CUST_MOD_DATE
2024-06-01 00:00:00	0	2024-06-03 00:00:00
2024-06-02 00:00:00	0	2024-06-03 00:00:00
2024-06-03 00:00:00	20000	2024-06-03 00:00:00
2024-06-04 00:00:00	20000	2024-06-04 00:00:00
2024-06-05 00:00:00	20000	2024-06-05 00:00:00
2024-06-06 00:00:00	20000	2024-06-06 00:00:00
2024-06-07 00:00:00	20000	2024-06-07 00:00:00
2024-06-08 00:00:00	0	2024-06-10 00:00:00
2024-06-09 00:00:00	0	2024-06-10 00:00:00
2024-06-10 00:00:00	20000	2024-06-10 00:00:00
2024-06-11 00:00:00	20000	2024-06-11 00:00:00

fiddle