Solved

ORACLE SQL filter help needed

Posted on 2014-02-05
9
493 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PL/SQL Two PAYER_IDs with PAYER_TYPES these two types 4 35
EXECUTE IMMEDIATE 5 53
Oracle -- identify blocking session 24 42
passing parameters to sql script oracle 4 26
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

911 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

22 Experts available now in Live!

Get 1:1 Help Now