ORA-01427: single-row subquery returns more than one row in a CASE statement

I'm getting the ORA-01427 error with the following code:

update rpt_group a
set a.rpt_category_id = 
case
  when ((select c.control from grpmisc c
 where (c.grp = a.grp)) = '01') then '201'
  when ((select c.control from grpmisc c
 where (c.grp = a.grp)) = '02') then '202'
  when ((select c.control from grpmisc c
 where (c.grp = a.grp)) = '03') then '203'
 else '93'
   end
where rpt_category_id = '93';

But when I simply do, say

select c.control from grpmisc c, rpt_group a
 where c.grp = a.grp and a.grp = '01'

It returns no rows. This is the same for '02' and '03'. So why do I get the "subquery re开发者_开发技巧turns more than one row" error?

Thanks..

---

You're running this test...
select c.control from grpmisc c, rpt_group a where c.grp = a.grp and a.grp = '01'

But your sub queries are formed at this...
select c.control from grpmisc c, rpt_group a where c.grp = a.grp

If you run the latter of the two queries, I bet you get many records? This means that either, you're missing the and a.grp = '01' from your sub-queries, or you need to limit your results to just one record...

A couple of options could be...

(select c.control from grpmisc c where c.grp = a.grp group by c.control)

(select c.control from grpmisc c where c.grp = a.grp and rownum < 2 order by <blah>)

---

there are at least three places in your code where such error may occure. you should limit subqueries so they could return only one row. e.g.

...
case
  when ((select c.control from grpmisc c
 where (c.grp = a.grp) and rownum < 2) = '01') then '201'
...