Oracle - Subquery in IF Clause

Question

I have the following code:

if USER IN (SELECT user_id from user_maint where roll = 12) THEN
        p_status := 'W';

ELSE 
        p_status := 'A';
END IF;

It gives the error: Error(332,16): PLS-00405: subquery not allowed in this context

How to make subquery in the IF clause?

That's a function which returns the user_id from the current Oracle session, @Kazi. — Littlefoot, May 30 '21 at 09:07
*How to make subquery in the IF clause?* makes the assumption that this is something PL/SQL can do. What you really want to do is test a condition using a query. — William Robertson, May 30 '21 at 11:55

score 3 · Accepted Answer · answered May 30 '21 at 09:06

Select first, use in if next.

declare
  l_user_id user_maint.user_id%type;
  p_status  varchar2(1);
begin
  select user_id
  into l_user_id
  from user_maint
  where roll = 12;

  if l_user_id = user then
     p_status := 'W';
  else
     p_status := 'A';
  end if;
end;
/

Though, a simpler option is

select case when user_id = user then 'W'
            else 'A'
       end
into p_status
from user_maint
where roll = 12;

Oracle - Subquery in IF Clause

1 Answers1