I have the following SQL for an Oracle database. There are 2 tables: EMP_Employee which contains employee names and locations and EMP_PhoneNumbers which contains phone numbers for the employees. The SQL joins the two tables and gets the Home Phone, Mobile Phone and Business Mobile Phone numbers. Employees can have 0, 1, 2, or 3 of the phone numbers.
SELECT e.FirstName, e.LastName, e.LocationID, e.EmailAddress,
(SELECT a.PhoneNumber FROM EMP_OWNER.EMP_PhoneNumbers a WHERE a.EmployeeID = e.EmployeeID AND a.PhoneTypeID = 5 AND a.EmergencyNotificationSystem = 'Y') AS HomePhone,
(SELECT b.PhoneNumber FROM EMP_OWNER.EMP_PhoneNumbers b WHERE b.EmployeeID = e.EmployeeID AND b.PhoneTypeID = 6 AND b.EmergencyNotificationSystem = 'Y') AS MobilePhone,
(SELECT c.PhoneNumber FROM EMP_OWNER.EMP_PhoneNumbers c WHERE c.EmployeeID = e.EmployeeID AND c.PhoneTypeID = 4 AND c.EmergencyNotificationSystem = 'Y') AS BusinessMobilePhone
FROM EMP_OWNER.EMP_Employee e
LEFT JOIN EMP_OWNER.EMP_PhoneNumbers p ON p.EmployeeID = e.EmployeeID
WHERE TerminationDate IS NULL
ORDER BY LastName, FirstName
Everything returns as expected but now I want to filter the data so I only get records that have a phone number in at least one of the types. I tried the following WHERE clause but received an error: ORA-00904: "BUSINESSMOBILEPHONE": invalid identifier
WHERE TerminationDate IS NULL AND (HomePhone IS NOT NULL OR MobilePhone IS NOT NULL OR BusinessMobilePhone IS NOT NULL)
Any help is greatly appreciated!