• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 508
  • Last Modified:

ORACLE SQL filter help needed

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

Open in new window


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!
0
dyarosh
Asked:
dyarosh
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
select FirstName,
       LastName,
       LocationID,
       EmailAddress,
       HomePhone,
       MobilePhone,
       BusinessMobilePhone
  from (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)
 where HomePhone is not null
    or MobilePhone is not null
    or BusinessMobilePhone is not null;

Open in new window

0
 
sdstuberCommented:
select * from
(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)
where coalesce(homephone,mobilephone,businessmobilephone) is not null
ORDER BY LastName, FirstName
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
But you should get rid of those correlated sub-queries. You already join the EMP_OWNER.EMP_PhoneNumbers table, so there is no need to hit them more than neccessary ;-)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
vinurajrCommented:
select * From (
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)
where (HomePhone IS NOT NULL OR MobilePhone IS NOT NULL OR BusinessMobilePhone IS NOT NULL)
ORDER BY LastName, FirstName
0
 
vinurajrCommented:
select * From (
SELECT e.FirstName, e.LastName, e.LocationID, e.EmailAddress,
case when e.PhoneTypeID = 5 then a.PhoneNumber end AS HomePhone,
case when e.PhoneTypeID = 6 then a.PhoneNumber end AS MobilePhone,
case when e.PhoneTypeID = 4 then a.PhoneNumber end AS BusinessMobilePhone
FROM EMP_OWNER.EMP_Employee e
LEFT JOIN EMP_OWNER.EMP_PhoneNumbers p ON p.EmployeeID = e.EmployeeID
WHERE TerminationDate IS NULL )
where (HomePhone IS NOT NULL OR MobilePhone IS NOT NULL OR BusinessMobilePhone IS NOT NULL)
ORDER BY LastName, FirstName
0
 
sdstuberCommented:
slightly more efficient version - only need to use the phone number table once
and eliminates the need for an outer join


SELECT *
  FROM (SELECT e.firstname,
               e.lastname,
               e.locationid,
               e.emailaddress,
               MAX(CASE WHEN phonetypeid = 5 THEN p.phonenumber END) homephone,
               MAX(CASE WHEN phonetypeid = 6 THEN p.phonenumber END) mobilephone,
               MAX(CASE WHEN phonetypeid = 4 THEN p.phonenumber END) businessmobilephone
          FROM emp_owner.emp_employee e
               INNER JOIN emp_owner.emp_phonenumbers p
                   ON p.employeeid = e.employeeid
                  AND p.phonetypeid IN (4, 5, 6)
                  AND p.emergencynotificationsystem = 'Y'
         WHERE terminationdate IS NULL
        GROUP BY e.firstname,
                 e.lastname,
                 e.locationid,
                 e.emailaddress)
 WHERE COALESCE(homephone, mobilephone, businessmobilephone) IS NOT NULL
ORDER BY lastname, firstname
0
 
vinurajrCommented:
above comment onlu brings the Ph number which matched with Employee table.
0
 
sdstuberCommented:
>> Ph number which matched with Employee table.

of course.  The "where not null"  condition of the question requires that at least one phone number is found for the employee.  You can't have a not-null value if you can't match at least one

That means the outer join provides no functionality except to make the query less efficient
0
 
dyaroshAuthor Commented:
Thanks.  I appreciate the quick responses!
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now