Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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
add comment

migrated from stackoverflow.com Oct 26 '12 at 12:52

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 3 down vote accepted

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)
share|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
    
You're welcome :) –  Florin Ghita Oct 26 '12 at 11:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.