ORA-01427: single-row subquery returns more than one row error | But I want multiple values

0
votes

I've below query, into which I'm creating using a subquery which is returning

ORA-01427: single-row subquery returns more than one-row error.

But I want all the values that subquery is returning, and there is no other column left for the join condition. Below is my sample query.

select name, 
       dob, 
       cdate, 
      (select value 
         from item a, 
              books b 
        where a.id = b.id 
          and a.newid = b.newid 
          and a.id = s.id 
          and a.bid = s.cid
          and a.eventid=1) col_value,
      (select value2
         from item a, 
              books b 
        where a.id = b.id 
          and a.newid = b.newid 
          and a.id = s.id 
          and a.bid = s.cid
          and a.eventid=1) col_value2
  from sample s, 
       purchase p
 where s.id = p.id
   and s.cid = p.cid

Desired Output

enter image description here

Do I need to apply a Group By? Please let me know your suggestions.

2
sqloracle
LEFT JOIN the subquery instead. – jarlh
Tip of today: Always use modern, explicit JOIN syntax. Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed! – jarlh
Please use modern JOIN syntax, available since SQL-92, that is 28 years ago. – The Impaler
So, if you have three values, which one do you want? min, max, avg, sum? – The Impaler
You said you "want all values". I think that means you need to use listagg. – Jeff Holt

2 Answers

2
votes

It is little bit more difficult without data but try:

select name -- please use table alias so you know which table the value is from (s, p or cv)
       , dob
       , cdate
       , cv.value
from sample s 
left join purchase p on s.id=p.id and s.cid=p.cid --or just join
left join (select value, a.id, a.bid  --or just join
           from item a
           left join books b  --or just join
           on a.id=b.id and a.newid=b.newid) cv
on cv.id = s.id and cv.bid = s.cid           
left join (select value2
           from item a 
           left join books b 
           on a.id = b.id and a.newid = b.newid) cv2
on cv2.id = s.id and cv2.bid = s.cid
where cv2.eventid=1;
0
votes
select name, 
       dob, 
       cdate, 
      (select value 
         from  
              books b 
        where a.id = b.id 
          and a.newid = b.newid ) col_value,
      (select value2
         from 
              books b 
        where a.id = b.id 
          and a.newid = b.newid ) col_value2
  from sample s, 
       purchase p,item a
 where s.id = p.id
   and s.cid = p.cid
   and a.id(+)=s.id
   and a.bid(+)=s.cid
   and a.eventid(+)=1