Solved

ORACLE SQL filter help needed

Posted on 2014-02-05
9
501 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 74

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 74

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 74

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

738 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