Below is a table using which I am trying to establish a table of content shown in table B.

Table A

Table B

I am trying to accumulate and get a result of Total and Value until both are defined (i.e. Row-3, Row 7) using SELECT statement. I have tried to use LEAD and LAG to check if both values are defined in table but its not working for me.

CodePudding user response：

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

SELECT *
FROM   A
MATCH_RECOGNIZE(
  ORDER BY sno
  MEASURES
    SUM(total) AS total,
    SUM(value) AS value
  PATTERN (undefined* defined)
  DEFINE
    undefined AS total = 0 OR value = 0,
    defined   AS total > 0 AND value > 0
)

Which, for the sample data:

CREATE TABLE A (Sno, Total, Value) AS
SELECT 1,   0, 1   FROM DUAL UNION ALL
SELECT 2, 225, 0   FROM DUAL UNION ALL
SELECT 3, 210, 2   FROM DUAL UNION ALL
SELECT 4,   0, 0   FROM DUAL UNION ALL
SELECT 5,   0, 0.5 FROM DUAL UNION ALL
SELECT 6, 150, 0   FROM DUAL UNION ALL
SELECT 7, 150, 0.5 FROM DUAL;

Outputs:

fiddle