If you outer join ems.phone ph you will do this probably for all columns,not only for the id:
AND (ph.phone_type_code(+) = 'M' and ph.phone_number(+) is NULL
OR ph.phone_type_code(+) = 'W1' and ph.phone_number(+) is NULL)
After rereading the question i would change the above to:
AND ph.phone_type_code(+) in ( 'M' ,'W1')
AND ph.phone_number(+) is null
Message was edited by: chris227 extended
Can you try doing an additional join with the phone table? Something like this..
/* Formatted on 8/27/2013 9:04:25 AM (QP5 v5.215.12089.38647) */
SELECT DISTINCT emp.person_id,
FROM ems.employee emp,
WHERE TO_NUMBER (SUBSTR (emp.gl_header, 9, 2)) = dep.department_id
AND emp.loc_id = loc.loc_id
AND emp.person_id = ph.person_id(+)
AND ph1.person = ph.person_id
AND ph.phone_type_code = 'M'
AND ph.phone_number IS NULL
AND ph1.phone_type_code = 'W1'
AND ph1.phone_number IS NULL
ORDER BY emp.person_id, loc.location_desc, dep.department_desc;
use paranthesis very well
AND ((ph.phone_type_code = 'M') and (ph.phone_number is NULL)
OR (ph.phone_type_code = 'W1') and (ph.phone_number is NULL))
ORDER BY emp.person_id,loc.location_desc,dep.department_desc ;
This should change nothing. Read about operator precendence in the oracle sql docs.