CODEWITHSUNDEEP
sqloracle-database
Geek
Geek

Reputation: 3329

oracle not in and exists functionality

I have a query with output as below:

select c.case_id caseId, c.memberId memberId, c.last_name lastName, c.first_name firstName, lkp_alt.descr altShowCauseAuthority, 
 'Active' as caseType,
 listagg(lkp_cs.descr,',') within group (order by lkp_cs.id) as caseStatus
 from cases c join lkP_alt_show_cause_authority lkp_alt
 on c.ATL_SHOW_CAUSE_AUTHORITY = lkp_alt.id
 and c.case_type = 'P'
 join case_status cs
 on cs.case_id = c.case_id
 join lkp_case_status lkp_cs
 on lkp_cs.id = cs.case_status_id 
 where (c.created_by = 1 and c.assigned_to is null)
 or (c.assigned_to = 1) and c.delete_date is null
 group by c.case_id, c.memberId, c.last_name, c.first_name, lkp_alt.descr, c.case_type;

Output:

caseId  memberId    lastName    firstName   altShow caseType caseStatus
101     1365385501  WWW         test        MARFORK Active   Active ,Closed ,EO         
102     1501534761  AAA         test        MARFORK Active   IGMC,JPAS                  
110     1572158078  CCC         test        I MEF   Active   EO,EEO

Case_status is as follows:

case_id case_status_id case_status_date
101     1               17-OCT-21
101     2//Closed       17-NOV-21
101     3               17-OCT-21
102     4               17-OCT-21
102     5               17-OCT-21
110     8               17-OCT-21
110     9               17-OCT-21

Now I want my query to display only cases that are not closed, which means i shouldnt display case 101 since it has a case status closed. I tried using "and c.case_status_id != 2" which just doesnt display closed under status but doesnt eliminate the record from display. Any suggestions please.

Upvotes: 0

Views: 44

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

You can try the below query -

SELECT c.case_id caseId,
       c.memberId memberId,
       c.last_name lastName,
       c.first_name firstName,
       lkp_alt.descr altShowCauseAuthority,
       'Active' as caseType,
       listagg(lkp_cs.descr,',') within group (order by lkp_cs.id) as caseStatus
  FROM cases c
  JOIN lkP_alt_show_cause_authority lkp_alt ON c.ATL_SHOW_CAUSE_AUTHORITY = lkp_alt.id
                                           AND c.case_type = 'P'
  JOIN case_status cs ON cs.case_id = c.case_id
  JOIN lkp_case_status lkp_cs ON lkp_cs.id = cs.case_status_id
 WHERE ((c.created_by = 1 AND c.assigned_to IS NULL)
        OR (c.assigned_to = 1) AND c.delete_date IS NULL)
   AND NOT EXISTS (SELECT NULL
                     FROM cases c1
                     JOIN case_status cs1 ON cs1.case_id = c1.case_id
                    WHERE c.case_id = c1.case_id
                      AND cs1.case_status_id = 2)
 GROUP BY c.case_id,
          c.memberId,
          c.last_name,
          c.first_name,
          lkp_alt.descr,
          c.case_type;

Upvotes: 0

Del
Del

Reputation: 1599

I think it is probably easier to just use an analytical function to determine if it is closed. That saves you from having to access the same table multiple times. Something like this:

SELECT c.case_id caseid,
       c.memberid memberid,
       c.last_name lastname,
       c.first_name firstname,
       lkp_alt.descr altshowcauseauthority,
       'Active' AS casetype,
       listagg(lkp_cs.descr, ',') within GROUP(ORDER BY lkp_cs.id) AS casestatus
FROM cases c
INNER JOIN lkp_alt_show_cause_authority lkp_alt ON c.atl_show_cause_authority = lkp_alt.id AND c.case_type = 'P'
INNER JOIN (SELECT case_id, 
                   case_status_id,
                   COUNT(DECODE(case_status_id, 2, 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
JOIN lkp_case_status lkp_cs ON lkp_cs.id = cs.case_status_id
WHERE (c.created_by = 1 AND c.assigned_to IS NULL)
OR (c.assigned_to = 1)
AND c.delete_date IS NULL
GROUP BY c.case_id, c.memberid, c.last_name, c.first_name, lkp_alt.descr, c.case_type;

Upvotes: 1

Related Questions