[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Oracle SQL Help

Posted on 2013-07-01
15
Medium Priority
?
333 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
[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
  • 5
  • 4
  • 2
  • +2
15 Comments
 
LVL 32

Expert Comment

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

Accepted Solution

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

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

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 32

Assisted Solution

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

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
 
LVL 49

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 49

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 49

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 14

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 49

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

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!

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

649 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