Solved

ORACLE SQL filter help needed

Posted on 2014-02-05
9
488 Views
Last Modified: 2014-02-05
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
Comment
Question by:dyarosh
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 250 total points
ID: 39835631
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39835635
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39835640
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
 
LVL 8

Expert Comment

by:vinurajr
ID: 39835646
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Expert Comment

by:vinurajr
ID: 39835665
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 39835682
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
 
LVL 8

Expert Comment

by:vinurajr
ID: 39835697
above comment onlu brings the Ph number which matched with Employee table.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39835713
>> 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
 

Author Closing Comment

by:dyarosh
ID: 39835971
Thanks.  I appreciate the quick responses!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now