troubleshooting Question

Subquery in a case statement in plsql throws the error

Avatar of sam_2012
sam_2012 asked on
* Oracle PL/SQLSQL
3 Comments1 Solution166 ViewsLast Modified:
Hi Team ,

I getting the below error when i run the  code in plsql  , ideally we should be able to run queries in case statement  , but not sure why it does not work in plsql program
Any help is really appreciated. Is it not possible to  write a subquery in the case statment in plsql unlike sql ?

declare
l_deptid number(10):=&1;
l_comments varchar2(40):=null;
begin

case
when (select count(*) from employees where department_id=l_deptid)>=40 then
      l_comments:='Highly Size department';
else
      l_comments:='Small Size department';
end case;

dbms_output.put_line(' Department is '|| l_comments);
end;
/


Error report:
ORA-06550: line 7, column 7:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

   ( - + case mod new not null <an identifier>
   <a double-quoted delimited-identifier> <a bind variable>
   continue avg count current exists max min prior sql stddev
   sum variance execute forall merge time timestamp interval
   date <a string literal with character set specification>
   <a number> <a single-quoted SQL string> pipe
   <an alternatively-quoted string literal with character set specification>
   <an alternat
ORA-06550: line 7, column 66:
PLS-00103: Encountered the symbol ")" when expecting one of the following:

   . ( * @ % & - + ; / at for mod remainder rem
   <an exponent (**)> and or group having intersect minus order
   start union where connect || multiset
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros