Duplicate row if: ORA-01427: single-row subquery returns more than one row

Question

I got these three tables s,a and p. a and s got 1:1 relation. But a to p got 1:x relation. My current query is only build for 1:0 or 1:1 relation. Currently I'm getting: "ORA-01427: single-row subquery returns more than one row." If there's more than one resource_id for an activity_no

So how do I redo my query so If there's two or more resource_id for an activity_id I want to duplicate the selected row two or more times depending on how many resource_id there is for an activity_no?

I've been looking on: Oracle, insert multirows from subquery with more than one row but It doesn't work.

    select s.sub_project_id,  
           a.activity_no,
           (select p.resource_id
            from p
            where p.project_id = 'PROPSTOT'
            and p.activity_seq = a.activity_seq,                                     
from s,
     a
where s.sub_project_id = a.sub_project_id
      and s.project_id = 'PROPSTOT' 
      and a.project_id = 'PROPSTOT'

sorry for the confusion but AN100 should be changed to PROPSTOT — President Camacho, Jun 05 '13 at 10:32

score 2 · Answer 1 · 2013-06-05T11:03:31.213

2

Try joining to the table in the query, like so:

select s.sub_project_id,  
       a.activity_no,
       p.resource_id
from s
join a on s.sub_project_id = a.sub_project_id and a.project_id = 'PROPSTOT'
left join p on p.project_id = 'PROPSTOT' and p.activity_seq = a.activity_seq
where s.project_id = 'PROPSTOT'

edited Jun 05 '13 at 11:03

answered Jun 05 '13 at 10:26

Not all activity_no have a resource_id. Thats why I've a seperate select query for it. If else the query ignores rows without resource_id. Check my updated OP, ANS100 is edited to PROPSTOT. – President Camacho Jun 05 '13 at 10:35

score 1 · Accepted Answer · 2013-06-05T10:35:06.877

1

It's not clear to me what exactly you are trying to do but from your description it sounds you simly want a join:

select s.sub_project_id,  
       a.activity_no,
       p.resource_id
from s 
   join a on s.sub_project_id = a.sub_project_id and a.project_id = s.project_id
   left outer join p on p.activity_seq = a.activity_seq
where s.project_id = 'PROPSTOT'

edited Jun 05 '13 at 10:35

answered Jun 05 '13 at 10:27

Not all activity_no have a resource_id. Thats why I've a seperate select query for it. If else the query ignores rows without resource_id. Check my updated OP, ANS100 is edited to PROPSTOT. – President Camacho Jun 05 '13 at 10:34
@JohnStokes: then you want an outer join. – Jun 05 '13 at 10:36
@JohnStokes: then you are not running the query from my answer. That one can **not** produce an ORA-01427. – Jun 05 '13 at 10:41
Nevermind removed my subquery for p.resource_id and It worked! – President Camacho Jun 05 '13 at 10:41

Duplicate row if: ORA-01427: single-row subquery returns more than one row

2 Answers2