Issue adding extra predicate into exists subquery

View All Public Scripts Login to Run Script

Script Name Issue adding extra predicate into exists subquery
Visibility Unlisted - anyone with the share link can access
Description Related to https://stackoverflow.com/questions/56354606/exists-not-working-with-subquery-in-with-clause?noredirect=1#comment99315741_56354606
Contributor Boneist
Created Wednesday May 29, 2019

Statement 1

CREATE TABLE ReferenceDateTable  
    ( 
     ItemId number, 
     ReferenceDate date,  
     Type varchar2(1) 
    )

Table created.

Statement 2

INSERT INTO ReferenceDateTable (ItemId, ReferenceDate, Type) 
select 1, to_date('19000201', 'YYYYMMDD'), '1' from dual union all 
select 1, to_date('19000202', 'YYYYMMDD'), '1' from dual union all 
select 1, to_date('19000203', 'YYYYMMDD'), '2' from dual union all 
select 1, to_date('19000204', 'YYYYMMDD'), '1' from dual union all 
select 1, to_date('19000205', 'YYYYMMDD'), '1' from dual

5 row(s) inserted.

Statement 3

CREATE TABLE ResultTable  
    ( 
     ItemId number, 
     ReferenceDate date,  
     Value number 
    )

Table created.

Statement 4

INSERT INTO ResultTable (ItemId, ReferenceDate, Value) 
select 1, to_date('19000201', 'YYYYMMDD'), 1 from dual union all 
select 1, to_date('19000202', 'YYYYMMDD'), 2 from dual union all 
select 1, to_date('19000203', 'YYYYMMDD'), 3 from dual union all 
select 1, to_date('19000204', 'YYYYMMDD'), 4 from dual union all 
select 1, to_date('19000205', 'YYYYMMDD'), 5 from dual

5 row(s) inserted.

Statement 5

CREATE TABLE StartDateTable 
    ( 
     ItemId number, 
     StartDate date 
    )

Table created.

Statement 6

INSERT INTO StartDateTable (ItemId, StartDate) VALUES (1, to_date('19000101', 'YYYYMMDD'))

1 row(s) inserted.

Statement 7
```
commit
```
```
Statement processed.
```

Statement 8

WITH FirstDateFilter AS ( 
    SELECT ReferenceDate, 
           Type, 
           LAG(Type, 1, 0) OVER (ORDER BY ReferenceDate) AS PreviousType 
    FROM ReferenceDateTable 
    WHERE ItemId = 1 
    AND   ReferenceDate <= to_date('19000205', 'YYYYMMDD') 
    AND   Type IN ('1', '2') 
), SecondDateFilter AS ( 
    SELECT ReferenceDate 
    FROM FirstDateFilter 
    WHERE ReferenceDate >= to_date('19000201', 'YYYYMMDD') 
    --AND   ReferenceDate >= ( SELECT StartDate FROM StartDateTable WHERE ItemId = 1 ) 
    AND   Type = '1' 
    AND   PreviousType = '1' 
) 
SELECT ReferenceDate, Value 
FROM ResultTable 
WHERE ItemId = 1 
AND EXISTS ( SELECT * FROM SecondDateFilter WHERE SecondDateFilter.ReferenceDate = ResultTable.ReferenceDate )


REFERENCEDATE VALUE
02-FEB-00 2
05-FEB-00 5

2 rows selected.

REFERENCEDATE	VALUE
02-FEB-00	2
05-FEB-00	5

Statement 9

WITH FirstDateFilter AS ( 
    SELECT ReferenceDate, 
           Type, 
           LAG(Type, 1, 0) OVER (ORDER BY ReferenceDate) AS PreviousType 
    FROM ReferenceDateTable 
    WHERE ItemId = 1 
    AND   ReferenceDate <= to_date('19000205', 'YYYYMMDD') 
    AND   Type IN ('1', '2') 
), SecondDateFilter AS ( 
    SELECT ReferenceDate 
    FROM FirstDateFilter 
    WHERE ReferenceDate >= to_date('19000201', 'YYYYMMDD') 
    AND   ReferenceDate >= ( SELECT StartDate FROM StartDateTable WHERE ItemId = 1 ) 
    AND   Type = '1' 
    AND   PreviousType = '1' 
) 
SELECT ReferenceDate, Value 
FROM ResultTable 
WHERE ItemId = 1 
AND EXISTS ( SELECT * FROM SecondDateFilter WHERE SecondDateFilter.ReferenceDate = ResultTable.ReferenceDate )


REFERENCEDATE VALUE
01-FEB-00 1
02-FEB-00 2
03-FEB-00 3
04-FEB-00 4
05-FEB-00 5

5 rows selected.

REFERENCEDATE	VALUE
01-FEB-00	1
02-FEB-00	2
03-FEB-00	3
04-FEB-00	4
05-FEB-00	5

Statement 10

WITH FirstDateFilter AS ( 
    SELECT ReferenceDate, 
           Type, 
           LAG(Type, 1, 0) OVER (ORDER BY ReferenceDate) AS PreviousType 
    FROM ReferenceDateTable 
    WHERE ItemId = 1 
    AND   ReferenceDate <= to_date('19000205', 'YYYYMMDD') 
    AND   Type IN ('1', '2') 
), SecondDateFilter AS ( 
    SELECT ReferenceDate 
    FROM FirstDateFilter 
    WHERE ReferenceDate >= to_date('19000201', 'YYYYMMDD') 
    --AND   ReferenceDate >= ( SELECT StartDate FROM StartDateTable WHERE ItemId = 1 ) 
    AND   Type = '1' 
    AND   PreviousType = '1' 
) 
select * from seconddatefilter


REFERENCEDATE
02-FEB-00
05-FEB-00

2 rows selected.

REFERENCEDATE
02-FEB-00
05-FEB-00

Statement 11

WITH FirstDateFilter AS ( 
    SELECT ReferenceDate, 
           Type, 
           LAG(Type, 1, 0) OVER (ORDER BY ReferenceDate) AS PreviousType 
    FROM ReferenceDateTable 
    WHERE ItemId = 1 
    AND   ReferenceDate <= to_date('19000205', 'YYYYMMDD') 
    AND   Type IN ('1', '2') 
), SecondDateFilter AS ( 
    SELECT ReferenceDate 
    FROM FirstDateFilter 
    WHERE ReferenceDate >= to_date('19000201', 'YYYYMMDD') 
    AND   ReferenceDate >= ( SELECT StartDate FROM StartDateTable WHERE ItemId = 1 ) 
    AND   Type = '1' 
    AND   PreviousType = '1' 
) 
select * from seconddatefilter


REFERENCEDATE
02-FEB-00
05-FEB-00

2 rows selected.

REFERENCEDATE
02-FEB-00
05-FEB-00

Statement 12

SELECT referencedate, VALUE 
FROM   resulttable rt 
WHERE  itemid = 1 
AND    EXISTS (SELECT NULL 
               FROM   (SELECT referencedate, 
                              TYPE, 
                              LAG(TYPE, 1, 0) OVER (ORDER BY referencedate) AS previoustype 
                       FROM   referencedatetable 
                       WHERE  itemid = 1 
                       AND    referencedate <= to_date('19000205', 'YYYYMMDD') 
                       AND    TYPE IN ('1', '2')) sdf 
               WHERE   sdf.referencedate >= to_date('19000201', 'YYYYMMDD') 
               --AND     sdf.referencedate >= (SELECT StartDate FROM StartDateTable WHERE ItemId = 1 ) 
               AND     sdf.TYPE = '1' 
               AND     sdf.previoustype = '1' 
               AND     sdf.referencedate = rt.referencedate)


REFERENCEDATE VALUE
02-FEB-00 2
05-FEB-00 5

2 rows selected.

REFERENCEDATE	VALUE
02-FEB-00	2
05-FEB-00	5

Statement 13

SELECT referencedate, VALUE 
FROM   resulttable rt 
WHERE  itemid = 1 
AND    EXISTS (SELECT NULL 
               FROM   (SELECT referencedate, 
                              TYPE, 
                              LAG(TYPE, 1, 0) OVER (ORDER BY referencedate) AS previoustype 
                       FROM   referencedatetable 
                       WHERE  itemid = 1 
                       AND    referencedate <= to_date('19000205', 'YYYYMMDD') 
                       AND    TYPE IN ('1', '2')) sdf 
               WHERE   sdf.referencedate >= to_date('19000201', 'YYYYMMDD') 
               AND     sdf.referencedate >= (SELECT StartDate FROM StartDateTable WHERE ItemId = 1 ) 
               AND     sdf.TYPE = '1' 
               AND     sdf.previoustype = '1' 
               AND     sdf.referencedate = rt.referencedate)


REFERENCEDATE VALUE
01-FEB-00 1
02-FEB-00 2
03-FEB-00 3
04-FEB-00 4
05-FEB-00 5

5 rows selected.

REFERENCEDATE	VALUE
01-FEB-00	1
02-FEB-00	2
03-FEB-00	3
04-FEB-00	4
05-FEB-00	5

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database