Operation Details
Description: Join across CaseHeader and CaseParticipantRole. Retrieves case details based on the case participant role and typeCodes. Also filters by startDate on the caseHeader table.
Sterotype: nsmulti
SQL 
SELECT
  CaseHeader.caseID,
  CaseHeader.caseReference,
  CaseHeader.integratedCaseID,
  CaseHeader.integratedCaseType,
  CaseHeader.caseTypeCode,
  CaseHeader.startDate,
  CaseHeader.endDate,
  CaseHeader.statusCode,
  CaseParticipantRole.typeCode
INTO
  :caseID,
  :caseReference,
  :integratedCaseID,
  :integratedCaseType,
  :caseTypeCode,
  :startDate,
  :endDate,
  :statusCode,
  :caseParticipantRoleType 
FROM
  CaseHeader,
  CaseParticipantRole 
WHERE CaseHeader.caseID = CaseParticipantRole.caseID 
  AND CaseParticipantRole.participantRoleID = :participantRoleID 
  AND 
  (
    CaseParticipantRole.typeCode = :typeCode1 
    OR CaseParticipantRole.typeCode = :typeCode2 
  ) 
  AND 
  (
    CaseHeader.startDate >= :startDateFrom 
    OR :startDateFrom IS NULL 
  ) 
  AND 
  (
    CaseHeader.startDate <= :startDateTo 
    OR :startDateTo IS NULL 
  ) 
ORDER BY startDate DESC