oracle moving where clause from join to outer join - not returning records properly-CodePudding

I have a query which as follows:

select c.* from cases c
    left outer join (select case_id, case_status_id from case_status where case_id not in (SELECT case_id
     FROM case_status
     where (case_status_id = 16 and case_status_date < sysdate - 365))) cs ON cs.case_id = c.case_id 
     left outer join lkp_case_status lkp_cs
     on lkp_cs.id = cs.case_status_id 
     where c.case_type = 'P'
     and c.delete_date is null

The 2nd line used to be a join earlier but now I had to convert it to left outer join. What it does is it checks for case status and if the case status is 16 and the date is a year back don't show the record. When I change it to left outer join it picks up the record even when the dates are in 2019.

case

id  name    
1   AAA
2   BBB
3   CCC
4   DDD

case_status

1   16 01-NOV-19 03.42.37.420000000 PM
1   5 01-NOV-19 03.42.37.420000000 PM
2   1  18-NOV-19 12.36.11.268000000 PM
2   3  18-NOV-19 12.36.11.268000000 PM
3   5  18-NOV-21 12.36.11.268000000 PM
3   16 18-NOV-21 12.36.11.268000000 PM

The output should show as follows:

Result:

id name status
2   BBB 1,3
3   CCC 5,16
4   DDD

case id 1 has status 16 and dates back to 2019, case id 3 though has status 16 is within a year so should be picked up. The query is really big but I am missing only this case status scenario hence posting a part of it.

Any suggestions or inputs, please.

CodePudding user response：

How about not exists?

Sample data:

SQL> with
  2  tcase (id, name) as
  3    (select 1, 'AAA' from dual union all
  4     select 2, 'BBB' from dual union all
  5     select 3, 'CCC' from dual union all
  6     select 4, 'DDD' from dual
  7    ),
  8  tcase_status (case_id, case_status_id, case_status_date) as
  9    (select 1, 16, date '2019-11-01' from dual union all
 10     select 1,  5, date '2019-11-01' from dual union all
 11     select 2,  1, date '2019-11-18' from dual union all
 12     select 2,  3, date '2019-11-18' from dual union all
 13     select 3,  5, date '2021-11-18' from dual union all
 14     select 3, 16, date '2021-11-18' from dual
 15    )

Query:

 16  select c.id,
 17         c.name,
 18         listagg(cs.case_status_id, ', ') within group (order by cs.case_status_id) status
 19  from tcase c left join tcase_status cs on c.id = cs.case_id
 20  where not exists (select null
 21                    from tcase_status c
 22                    where c.case_id = cs.case_id
 23                      and c.case_status_id = 16
 24                      and c.case_status_date < add_months(trunc(sysdate), -12)
 25                   )
 26  group by c.id, c.name
 27  order by c.id;

        ID NAME STATUS
---------- ---- ----------
         2 BBB  1, 3
         3 CCC  5, 16
         4 DDD

SQL>