How to deal with queries with EXISTS clause in Cognos?

• How to deal with queries with EXISTS clause in Cognos?
  Here describes how to rewrite SQL queries containing EXISTS clause to Cognos compatible way using MINUS & INTERSECT.

  
  

  
  

  Original query:

  SELECT tc_lpn_id

    FROM lpn LP

    WHERE lpn_facility_status IN (45, 50, 55, 64)

    AND NOT EXISTS

      (SELECT 1

      FROM task_dtl TD

      WHERE TD.cntr_nbr = lp.tc_lpn_id

      AND TD.stat_code  < 90

      )

    AND EXISTS

      (SELECT 1

      FROM alloc_invn_dtl AD

      WHERE AD.cntr_nbr = lp.tc_lpn_id

      AND AD.stat_code  < 90

      )

  
  

  Modified for Cognos:

  SELECT tc_lpn_id FROM lpn LP WHERE lpn_facility_status IN (45, 50, 55, 64)

  minus

  select tc_lpn_id from lpn l, task_dtl t where t.cntr_nbr = l.tc_lpn_id and t.stat_code < 90

  intersect

  select tc_lpn_id from lpn l, alloc_invn_dtl a where a.cntr_nbr = l.tc_lpn_id and a.stat_code < 90

  
  

  
  

  Note:

  While rewriting queries, Intersect might eliminates duplicates. To avoid that ensure that correlated join condition column of parent is selected in select clause. This will overcome the problem.