CODEWITHSUNDEEP
oracle-databaseplsql
Sevval Kahraman
Sevval Kahraman

Reputation: 1301

Subquery In if Statement (plsql)

I want to find if p_param2 is in subquery then do some operations according to this result. This subquery returns more than 1 row.

  if p_param1 = 1
     and p_param2 in (select code from x_table where code is not null) then
    --..Some operations..
  end if;

But I get PLS-00405 error. How can write this code effectively?

Upvotes: 0

Views: 775

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

You need to get the needed value before IF..THEN statement within a seperate SQL query, while the current case is not possible. Try such a method which uses COUNT() aggregation without need of exception handling :

DECLARE
  p_param1 ...
  p_param2 ... 
  p_exists INT;
BEGIN
   SELECT SIGN( COUNT(*) )
     INTO p_exists
     FROM x_table 
    WHERE code IS NOT NULL 
      AND code = p_param2;
      
 IF p_param1 = 1 AND p_exists = 1 THEN
   -- some operations
 END IF;
END; 
/

Upvotes: 1

archimede
archimede

Reputation: 736

Declare a var v_dummy number. Then:

begin
   select 1
     into v_dummy
     from x_table
    where code = p_param2;

   exception
      when no_data_found then
         v_dummy := 0;
end;

if p_param1 = 1 and v_dummy = 1 then
.
.
.
end if;

If x_table.code can contain duplicate values, you have to decide what to do when more than one occurrence of p_param2 is found.

Upvotes: 1

Related Questions