[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ORACLE SQL filter help needed

Posted on 2014-02-05
9
Medium Priority
?
506 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 14

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 1000 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 14

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

872 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