Oracle error “too many values”, must be solved with nested SELECTs

oracleoracle-11gselect

I have hit upon a "too many values" error in Oracle, when working with some intricate nested SELECTs.

A simplified description of my problem, on a toy problem describing the hierarchy in a company. There are two tables:

  • ACG_EMPLOYEES, which lists all the employees

enter image description here

  • ACG_BOSS_OF, which simply lists who (b_id) is the boss of whom (e_id)

enter image description here

I can do

SELECT b_id as boss,
             LTRIM(MAX(SYS_CONNECT_BY_PATH(e_id,','))
             KEEP (DENSE_RANK LAST ORDER BY curr),',') as minion
FROM (SELECT b_id,
             e_id,
             ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) AS curr,
             ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) -1 AS prev
      FROM acg_boss_of)
GROUP BY b_id
CONNECT BY prev = PRIOR curr AND b_id = PRIOR b_id
START WITH curr = 1

to get the list of "minions" for every employee which is not a leaf in the hierarchy:
enter image description here

But let's say I also want to get other fields from the ACG_EMPLOYEES table, in this case the name for each boss:

enter image description here

I have tried 

SELECT 
emp.name, 
(SELECT b_id as boss,
             LTRIM(MAX(SYS_CONNECT_BY_PATH(e_id,','))
             KEEP (DENSE_RANK LAST ORDER BY curr),',') as minion
FROM (SELECT b_id,
             e_id,
             ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) AS curr,
             ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) -1 AS prev
      FROM acg_boss_of)
GROUP BY b_id
CONNECT BY prev = PRIOR curr AND b_id = PRIOR b_id
START WITH curr = 1) minion
FROM acg_employees emp

but I get the "too many values" error I mentioned.

I have seen some suggestions for solving this kind of issue; however, I cannot use any of them, since this statement will be used in a Product Lifecycle Management app for which this must be defined as a single SELECT, no matter how complicated (ok, I could use JOINs as well, but no stored procedures or such).

Best Answer

Using a join?

SELECT 
  minion.boss,
  emp.name, 
  minion.minion
FROM acg_employees emp 
JOIN    
    (SELECT b_id as boss,
                 LTRIM(MAX(SYS_CONNECT_BY_PATH(e_id,','))
                 KEEP (DENSE_RANK LAST ORDER BY curr),',') as minion
    FROM (SELECT b_id,
                 e_id,
                 ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) AS curr,
                 ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) -1 AS prev
          FROM acg_boss_of)
    GROUP BY b_id
    CONNECT BY prev = PRIOR curr AND b_id = PRIOR b_id
    START WITH curr = 1) minion
ON (minion.boss=emp.e_id)
Related Question