oracle outer join with condition in where clause-CodePudding

I have a case table and case_status which is a child table. Earlier case_status was a mandatory field but not anymore. So i had to change from join to left outer join. After adding this join condition the records are not getting picked up when case_status table doesnt have a record for a case. case_status has a lookup to lkp_case_status hence having an outer join even there. My query looks like this

select c.* from cases c
left outer join (SELECT case_id, 
                    case_status_id,
                    COUNT(DECODE(case_status_id, 16, 1, NULL)) OVER (PARTITION BY case_id) AS IS_CLOSED
             FROM case_status) 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  
 AND cs.is_closed = 0;

I think its the cs.is_closed = 0 is not letting the record to get pulled up. So i added AND cs.is_closed = 0; within the join condition as below,

select c.* from cases c
left outer join (SELECT case_id, 
                    case_status_id,
                    COUNT(DECODE(case_status_id, 16, 1, NULL)) OVER (PARTITION BY case_id) AS IS_CLOSED
             FROM case_status) cs ON cs.case_id = c.case_id AND cs.is_closed = 0
 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;

But this query is pulling all the records irrespective of the decode condition in the 3rd line. 3rd line - if case_status = 16, which means case is closed and dont pull the record.

Any inputs on how to make it work. Basically it has to pull all the records from cases table , if caseStatus table has a value of case_status = 16 dont pull the case record too.

case

id  name    
1   AAA
2   BBB
3   CCC

case_status

1   16
2   1

Output should show as follows:

Result:
id name
2   BBB
3   CCC

But what am getting is

id name
2   BBB

My query is not picking case with id = 3.

Any help is highly appreciated. Thank you.

CodePudding user response：

If you want to add a filtering predicate for an outer-joined table or table expression you'll need to account for their columns to show up as nulls. You can do:

select c.* from cases c
left outer join (SELECT case_id, 
                    case_status_id,
                    COUNT(DECODE(case_status_id, 16, 1, NULL)) 
                      OVER (PARTITION BY case_id) AS IS_CLOSED
             FROM case_status) 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  
 AND (cs.is_closed = 0 or cs.is_closed is null); -- changed here