Solved

Oracle SQL Help

Posted on 2013-07-01
15
325 Views
Last Modified: 2013-07-03
I have to create a view that shows the employee name and their work id, system id and login id.  The Employees are stored in the Employee table and their corresponding IDs are stored in the IDs table.  If I only needed to display one ID I can do that but I don't know how to display multiple IDs since the field names are the same.  Here are the table definitions:

EMP_Employee                       EMP_IDs                           EMP_IDType
=============                     ===========                  ==============
EmployeeID                            IDsID                              IDTypeID
FirstName                               EmployeeID                    IDType
LastName                                IDTypeID
                                                IDValue

EMP_IDType Data
================
1, WORK ID
5, SYSTEM ID
6, LOGIN ID

Here is the SQL I would use if I only needed to show one ID Type but I don't know how to display more than one.  Any help is greatly appreciated!
SELECT a.FirstName, a.LastName, b.IDValue
FROM EMP_OWNER.EMP_Employee a
LEFT JOIN EMP_OWNER.EMP_IDs b ON b.EmployeeID = a.EmployeeID
WHERE a.TerminationDate IS NULL AND b.IDTypeID = 1
ORDER BY a.LastName, a.FirstName

Open in new window

0
Comment
Question by:dyarosh
  • 5
  • 4
  • 2
  • +2
15 Comments
 
LVL 31

Expert Comment

by:awking00
ID: 39289921
Can you provide some sample data and your expected output?
0
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 400 total points
ID: 39289927
If you want to get the IDs within 1 row, you can do something like that:

select a.firstname,
       a.lastname,
       (select b.IDValue
          from EMP_OWNER.EMP_IDs b
         where b.EmployeeID = a.EmployeeID
           and b.IDTypeID = 1) login_id,
       (select b.IDValue
          from EMP_OWNER.EMP_IDs b
         where b.EmployeeID = a.EmployeeID
           and b.IDTypeID = 2) system_id,
       (select b.IDValue
          from EMP_OWNER.EMP_IDs b
         where b.EmployeeID = a.EmployeeID
           and b.IDTypeID = 3) work_id
  from EMP_OWNER.EMP_Employee a
 where <here goes your main where clause>;

Open in new window

0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39289938
or this (having Oracle 11g) in 1 column, comma separated:

select a.firstname,
       a.lastname,
       (select listagg(b.IDValue, ',') within group(order by null)
          from EMP_OWNER.EMP_IDs b
         where b.EmployeeID = a.EmployeeID) EMP_IDs
  from EMP_OWNER.EMP_Employee a
 where <here goes your main where clause>;

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289960
use field aliases for this, e.g.

SELECT
  a.FirstName
, a.LastName
, b.IDValue

, a.id     as a_id -- just specify the table|alias.field "as" <<a name you choose>>
, b.id     as b_id -- do this for any field, but particularly for 'common' field names

FROM EMP_OWNER.EMP_Employee a
LEFT JOIN EMP_OWNER.EMP_IDs b ON b.EmployeeID = a.EmployeeID
WHERE a.TerminationDate IS NULL AND b.IDTypeID = 1
ORDER BY a.LastName, a.FirstName
0
 

Author Comment

by:dyarosh
ID: 39290299
Alex, I tried your solution and I get the IDs that I need but I also get extra records.  I'm getting a record for each ID record.  I only want one record.  Here is the SQL I tried:

SELECT a.FirstName, a.LastName,
       (SELECT b.IDValue FROM EMP_OWNER.EMP_IDs b WHERE b.EmployeeID = a.EmployeeID AND b.IDTypeID = 1) AS EMPLOYEE_NBR,
       (SELECT b.IDValue FROM EMP_OWNER.EMP_IDs b WHERE b.EmployeeID = a.EmployeeID AND b.IDTypeID = 5) AS REP_NBR,
       (SELECT b.IDValue FROM EMP_OWNER.EMP_IDs b WHERE b.EmployeeID = a.EmployeeID AND b.IDTypeID = 6) AS SIEBEL_ID,
       (SELECT b.IDValue FROM EMP_OWNER.EMP_IDs b WHERE b.EmployeeID = a.EmployeeID AND b.IDTypeID = 3) AS DRC_ID,
       (SELECT b.IDValue FROM EMP_OWNER.EMP_IDs b WHERE b.EmployeeID = a.EmployeeID AND b.IDTypeID = 2) AS EMP_ID,
       (SELECT b.IDValue FROM EMP_OWNER.EMP_IDs b WHERE b.EmployeeID = a.EmployeeID AND b.IDTypeID = 4) AS EMP_NBR
FROM EMP_OWNER.EMP_Employee a
LEFT JOIN EMP_OWNER.EMP_IDs b ON b.EmployeeID = a.EmployeeID
WHERE a.TerminationDate IS NULL
ORDER BY a.LastName, a.FirstName

Open in new window


My output looks something like this:

FirstName, LastName, Employee_NBR, Rep_NBR, SIEBEL_ID, DRC_ID, EMP_ID, EMP_NBR
Jane, Doe, 12345, 012345, null, null, 012345, 712345
Jane, Doe, 12345, 012345, null, null, 012345, 712345
Jane, Doe, 12345, 012345, null, null, 012345, 712345
Jane, Doe, 12345, 012345, null, null, 012345, 712345
John, Doe, 23456, 023456, null, 1234567, 023456, 723456
John, Doe, 23456, 023456, null, 1234567, 023456, 723456
John, Doe, 23456, 023456, null, 1234567, 023456, 723456
John, Doe, 23456, 023456, null, 1234567, 023456, 723456
John, Doe, 23456, 023456, null, 1234567, 023456, 723456

The IDs are correct. I just get extra records.
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 100 total points
ID: 39290391
You can simply add a DISTINCT keyword - select distinct a.firstname, a.lastname, ...
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39291330
When using the statement above (sub-selects within the SELECT clause), you don't need the left join, like:

SELECT a.FirstName, a.LastName,
       (SELECT b.IDValue FROM EMP_OWNER.EMP_IDs b WHERE b.EmployeeID = a.EmployeeID AND b.IDTypeID = 1) AS EMPLOYEE_NBR,
       (SELECT b.IDValue FROM EMP_OWNER.EMP_IDs b WHERE b.EmployeeID = a.EmployeeID AND b.IDTypeID = 5) AS REP_NBR,
       (SELECT b.IDValue FROM EMP_OWNER.EMP_IDs b WHERE b.EmployeeID = a.EmployeeID AND b.IDTypeID = 6) AS SIEBEL_ID,
       (SELECT b.IDValue FROM EMP_OWNER.EMP_IDs b WHERE b.EmployeeID = a.EmployeeID AND b.IDTypeID = 3) AS DRC_ID,
       (SELECT b.IDValue FROM EMP_OWNER.EMP_IDs b WHERE b.EmployeeID = a.EmployeeID AND b.IDTypeID = 2) AS EMP_ID,
       (SELECT b.IDValue FROM EMP_OWNER.EMP_IDs b WHERE b.EmployeeID = a.EmployeeID AND b.IDTypeID = 4) AS EMP_NBR
FROM EMP_OWNER.EMP_Employee a
ORDER BY a.LastName, a.FirstName

Open in new window


Don't forget to put back the "TerminationDate" into the sub-selects if needed...
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 48

Expert Comment

by:PortletPaul
ID: 39291838
I wouldn't recommend correleated subqueries in the selection list, a simple group by will suffice and the explain plan will be lower cost I believe.
SELECT
       E.FirstName
     , E.LastName
     , MAX(CASE WHEN I.IDTypeID = 1 THEN I.IDValue ELSE NULL END) AS EMPLOYEE_NBR
     , MAX(CASE WHEN I.IDTypeID = 5 THEN I.IDValue ELSE NULL END) AS REP_NBR
     , MAX(CASE WHEN I.IDTypeID = 6 THEN I.IDValue ELSE NULL END) AS SIEBEL_ID
     , MAX(CASE WHEN I.IDTypeID = 3 THEN I.IDValue ELSE NULL END) AS DRC_ID
     , MAX(CASE WHEN I.IDTypeID = 2 THEN I.IDValue ELSE NULL END) AS EMP_ID
     , MAX(CASE WHEN I.IDTypeID = 4 THEN I.IDValue ELSE NULL END) AS EMP_NBR
FROM EMP_OWNER.EMP_Employee E
LEFT JOIN EMP_OWNER.EMP_IDs  I ON E.EmployeeID = I.EmployeeID
WHERE E.TerminationDate IS NULL
GROUP BY
       E.FirstName
     , E.LastName
ORDER BY
       E.LastName
     , E.FirstName

Open in new window

{+edit ooops some non-oracle syntax removed "AS E" "AS I"}
whilst here please also note that in this method you are doing far fewer scans/seeks of the data. Subqueries in the selection clause are "semi-joins" so 6 such subqueries = 6 semi-joins, plus each includes a where clause. PLUS, each of those subqueries must only return one row otherwise the overall query will error.
0
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 39292696
Hello,

You might want to try using "distinct" in order to get one row per id in your case:

SELECT distinct a.FirstName, a.LastName,
       (SELECT b.IDValue FROM EMP_OWNER.EMP_IDs b WHERE b.EmployeeID = a.EmployeeID AND b.IDTypeID = 1) AS EMPLOYEE_NBR,
       (SELECT b.IDValue FROM EMP_OWNER.EMP_IDs b WHERE b.EmployeeID = a.EmployeeID AND b.IDTypeID = 5) AS REP_NBR,
       (SELECT b.IDValue FROM EMP_OWNER.EMP_IDs b WHERE b.EmployeeID = a.EmployeeID AND b.IDTypeID = 6) AS SIEBEL_ID,
       (SELECT b.IDValue FROM EMP_OWNER.EMP_IDs b WHERE b.EmployeeID = a.EmployeeID AND b.IDTypeID = 3) AS DRC_ID,
       (SELECT b.IDValue FROM EMP_OWNER.EMP_IDs b WHERE b.EmployeeID = a.EmployeeID AND b.IDTypeID = 2) AS EMP_ID,
       (SELECT b.IDValue FROM EMP_OWNER.EMP_IDs b WHERE b.EmployeeID = a.EmployeeID AND b.IDTypeID = 4) AS EMP_NBR
FROM EMP_OWNER.EMP_Employee a
LEFT JOIN EMP_OWNER.EMP_IDs b ON b.EmployeeID = a.EmployeeID
WHERE a.TerminationDate IS NULL
ORDER BY a.LastName, a.FirstName

but i would not use this method.

what i would use in your case is something like this:

SELECT
       E.FirstName,
      E.LastName,
      enbr.IDValue EMPLOYEE_NBR,
     repnbr.IDTypeID REP_NBR,
      sbid.IDValue SIEBEL_ID,
     ....
FROM EMP_OWNER.EMP_Employee E,
EMP_OWNER.EMP_IDs  enbr,
EMP_OWNER.EMP_IDs  repnbr,
EMP_OWNER.EMP_IDs  sbid,
...
where E.EmployeeID = enbr.EmployeeID (+)
and enbr.IDTypeID = 1
and E.EmployeeID = repnbr.EmployeeID (+)
and enbr.IDTypeID = 5
and E.EmployeeID = sbid.EmployeeID (+)
and enbr.IDTypeID = 6
......
and E.TerminationDate IS NULL;

Open in new window


If you still get duplicate values throw a distinct after "select".
0
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 39292703
SELECT
       E.FirstName,
      E.LastName,
      enbr.IDValue EMPLOYEE_NBR,
     repnbr.IDTypeID REP_NBR,
      sbid.IDValue SIEBEL_ID,
     ....
FROM EMP_OWNER.EMP_Employee E,
EMP_OWNER.EMP_IDs  enbr,
EMP_OWNER.EMP_IDs  repnbr,
EMP_OWNER.EMP_IDs  sbid,
...
where E.EmployeeID = enbr.EmployeeID (+)
and enbr.IDTypeID (+)= 1
and E.EmployeeID = repnbr.EmployeeID (+)
and enbr.IDTypeID (+)= 5
and E.EmployeeID = sbid.EmployeeID (+)
and enbr.IDTypeID (+)= 6
......
and E.TerminationDate IS NULL;
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39292860
There is no need for 6 subqueries or 6 outer joins - a single, simple, group by is sufficient for this need. Results from some derived sample data:
FIRSTNAME	LASTNAME	EMPLOYEE_NBR	REP_NBR	SIEBEL_ID	DRC_ID	EMP_ID	EMP_NBR
Jane		Doe		12345		12345	(null)		(null)	12345	712345
John		Doe		23456		23456	(null)		1234567	23456	723456

Open in new window

see: http://sqlfiddle.com/#!4/5b121/11

please compare the explain plans, :
-- group by, 2 tables, scanned once each
http://sqlfiddle.com/#!4/5b121/11/0

-- correlated subqueries, EMP_EMPLOYEE used once, EMP_IDs 6 times
http://sqlfiddle.com/#!4/5b121/11/1

-- 6 outer joins, EMP_EMPLOYEE used once, EMP_IDs 6 times
http://sqlfiddle.com/#!4/5b121/11/2
this does also require distinct by the way

note there are no indexes at this sqlfiddle so explain plan results on-site would differ.
0
 

Author Closing Comment

by:dyarosh
ID: 39294023
Thanks to all for your help.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39295339
did you try using the more efficient 'group by' approach at all?
ID: 39291838

Please remember to be careful if using correlated subqueries in a selection clause, they can be very inefficient, and if they return more than a single value the query will error.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39295797
@dyarosh: My suggested "solution" was quick shot, hence you should pick Paul's answer(s) a the right one(s). His solution WILL outperform anything else mentioned here ;-)

@Paul: I hope, you'll get the points you deserve :-)

Kind regards
Alex
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39295810
:) thanks Alex, not chasing the points
- just that a more efficient method is at least explored.
Perhaps I didn't explain it well enough.
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

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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…

744 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

12 Experts available now in Live!

Get 1:1 Help Now