Subquery in a case statement in plsql throws the error
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)>=4
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:
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)>=4
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:
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
I would do that case in the select:
declare
l_comments varchar2(40):=null;
begin
select case
when count(*) > 40 then
'Highly Size department'
else
'Small Size department'
end
into l_comments
from employees where department_id=l_deptid;
dbms_output.put_line(' Department is '|| l_comments);
end;
/
ASKER