Hello All,
The query below brings back all of the cases which meet the criteria specified. The problem is that in some cases a Case will meet all but one criteria and will not show in the result set. What I would like is for the case to be returned with all the data it has and then leave the fields which have no data blank. I believe that this can be done with a Left Outer Join but I am not sure how to incorporate into this query. The with =358 is where I am running into problems.

Thanks for the Help
Justin
SELECT DISTINCT
(SC.CASE_NUM),
ALT.ALT_LEG_ENT_ID,
SO.OSJ_IND,
SO.NASD_REGSTR_IND,
SO.STATE_REGSTR_IND,
SO.DETACHED_OFC_IND,
P1.PERSONFIRSTNAM AS DS_FIRST_NAME,
P1.PERSONMIDDLENAM AS DS_MIDDLE_NAME,
P1.PERSONLASTNAM AS DS_LAST_NAME,
P2.PERSONFIRSTNAM AS MP_FIRST_NAME,
P2.PERSONMIDDLENAM AS MP_MIDDLE_NAME,
P2.PERSONLASTNAM AS MP_LAST_NAME,
SC.CASE_START_DTM,
P3.PERSONFIRSTNAM AS REVIEWER_FIRST_NAME,
P3.PERSONMIDDLENAM AS REVIEWER_MIDDLE_NAME,
P3.PERSONLASTNAM AS REVIEWER_LAST_NAME
FROM
FLDINSPT.ALT_LEG_ENT_ID ALT,
FLDINSPT.SUPERVISORY_OFFICE SO,
FLDINSPT.PERSON P1,
FLDINSPT.PERSON P2,
FLDINSPT.PERSON P3,
FLDINSPT.SUPER_CASE SC,
FLDINSPT.ROLE R1,
FLDINSPT.ROLE R2,
FLDINSPT.ROLE R3,
FLDINSPT.ROLE R4,
FLDINSPT.ROLE R5,
FLDINSPT.LEGAL_ENTITY LE1,
FLDINSPT.LEGAL_ENTITY LE2,
FLDINSPT.LEGAL_ENTITY LE3
WHERE
(SC.CASE_STATUS_CDE = 1 AND
SC.CASE_SUB_TYPE_CDE = 9 AND
SC.SUBJECT_NUM = R1.SUBJECT_NUM AND
R1.ROLE_TYPE_CDE = 2 AND
R1.LEGAL_ENTITY_ID = LE1.LEGAL_ENTITY_ID AND
LE1.LEGAL_ENTITY_ID = SO.LEGAL_ENTITY_ID) AND

(LE1.SUBJECT_NUM = R2.SUBJECT_NUM AND
R2.ROLE_TYPE_CDE = 357 AND
R2.LEGAL_ENTITY_ID = LE2.LEGAL_ENTITY_ID AND
LE2.LEGAL_ENTITY_ID = P2.LEGAL_ENTITY_ID) AND

(LE1.SUBJECT_NUM = R3.SUBJECT_NUM AND
R3.ROLE_TYPE_CDE = 358 AND
R3.LEGAL_ENTITY_ID = LE3.LEGAL_ENTITY_ID AND
LE3.LEGAL_ENTITY_ID = P1.LEGAL_ENTITY_ID) AND

(LE2.SUBJECT_NUM = R4.SUBJECT_NUM AND
R4.ROLE_TYPE_CDE = 359 AND
R4.LEGAL_ENTITY_ID = ALT.LEGAL_ENTITY_ID) AND

(SC.SUBJECT_NUM = R5.SUBJECT_NUM AND
R5.ROLE_TYPE_CDE = 1 AND
R5.LEGAL_ENTITY_ID = P3.LEGAL_ENTITY_ID)