Oracle SQL Help

Can you provide some sample data and your expected output?

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

Select allOpen in new window

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

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

Select allOpen 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.

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

Select allOpen in new window

Don't forget to put back the "TerminationDate" into the sub-selects if needed...

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

Select allOpen 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.

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;

Select allOpen in new window

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

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;

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

Select allOpen 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.

Thanks to all for your help.

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.

@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

:) thanks Alex, not chasing the points
- just that a more efficient method is at least explored.
Perhaps I didn't explain it well enough.