I have a table with periods and values I am trying to eliminate all pre-0 results when a 0 occurs in the table. I tried to apply some filters but failed
WITH PERIODS AS
(
SELECT TO_DATE('2020-01-01', 'YYYY-MM-DD') PERIOD, 1 value FROM dual UNION
SELECT TO_DATE('2020-01-02', 'YYYY-MM-DD') PERIOD, 2 value FROM dual UNION
SELECT TO_DATE('2020-01-03', 'YYYY-MM-DD') PERIOD, 3 value FROM dual UNION
SELECT TO_DATE('2020-01-04', 'YYYY-MM-DD') PERIOD, 4 value FROM dual UNION
SELECT TO_DATE('2020-01-05', 'YYYY-MM-DD') PERIOD, 5 value FROM dual UNION
SELECT TO_DATE('2020-01-06', 'YYYY-MM-DD') PERIOD, 0 value FROM dual UNION
SELECT TO_DATE('2020-01-07', 'YYYY-MM-DD') PERIOD, 6 value FROM dual UNION
SELECT TO_DATE('2020-01-08', 'YYYY-MM-DD') PERIOD, 7 value FROM dual UNION
SELECT TO_DATE('2020-01-09', 'YYYY-MM-DD') PERIOD, 8 value FROM dual UNION
SELECT TO_DATE('2020-01-10', 'YYYY-MM-DD') PERIOD, 9 value FROM dual
)
select * from PERIODS
full result set
01-JAN-20 1
02-JAN-20 2
03-JAN-20 3
04-JAN-20 4
05-JAN-20 5
06-JAN-20 0
07-JAN-20 6
08-JAN-20 7
09-JAN-20 8
10-JAN-20 9
Expected result
07-JAN-20 6
08-JAN-20 7
09-JAN-20 8
10-JAN-20 9
Another option (requires two table scans, though) would be
<snip>
23 SELECT *
24 FROM PERIODS
25 WHERE period > (SELECT period
26 FROM periods
27 WHERE VALUE = 0)
28 ORDER BY VALUE;
PERIOD VALUE
---------- ----------
2020-01-07 6
2020-01-08 7
2020-01-09 8
2020-01-10 9
SQL>