2

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).

  • Which version of Oracle are you using? – APC Oct 26 '12 at 11:01
  • what means single select? no Joins? – Florin Ghita Oct 26 '12 at 11:02
  • @APC It's Oracle Database 11g Release 11.1.0.7.0 - 64bit. – Cristina Oct 26 '12 at 11:04
  • 1
    @Florin Sorry, it was a bit unclear. I've updated my post: I can use JOINs and such, but no stored procedures, for instance. This SELECT will be part of a view the app creates. (Also: nice to see fellow compatriots on SO! :-) ) – Cristina Oct 26 '12 at 11:05
3

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)
|improve this answer|||||
  • Works perfectly, thanks! I couldn't see the forest for the trees anymore, can't believe it didn't occur to me! :D – Cristina Oct 26 '12 at 11:12
  • my problem is somewhat similar dba.stackexchange.com/questions/137336/… if u may help – Varun May 3 '16 at 9:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy