• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

Oracle SQL Help

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
dyarosh
Asked:
dyarosh
  • 5
  • 4
  • 2
  • +2
2 Solutions
 
awking00Commented:
Can you provide some sample data and your expected output?
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
PortletPaulfreelancerCommented:
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
 
dyaroshAuthor Commented:
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
 
awking00Commented:
You can simply add a DISTINCT keyword - select distinct a.firstname, a.lastname, ...
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
PortletPaulfreelancerCommented:
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
 
CarlsbergFTWCommented:
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
 
CarlsbergFTWCommented:
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
 
PortletPaulfreelancerCommented:
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
 
dyaroshAuthor Commented:
Thanks to all for your help.
0
 
PortletPaulfreelancerCommented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
@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
 
PortletPaulfreelancerCommented:
:) 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now