CODEWITHSUNDEEP
oracle-databaseselectif-statementconditional-statements
Krishhh
Krishhh

Reputation: 1

How to use select statement in an If condition

I have a variable which contains value '1'

current code: 

if var1 in (1, 2, 3, 4) then ...

Required format (I don't want to hard code the comparison values):

if var1 in (select col1 from table1) then ...

Select col1 from table1; 

1  
2  
3  
4

Upvotes: 0

Views: 107

Answers (3)

Tony BenBrahim
Tony BenBrahim

Reputation: 7300

Here is the proper way to do it:

declare
   rid rowid;
begin
  --prior logic
   begin
       select rowid into rid from table1 where table1.col1=var1;
       -- condition is met here, do the then part
   exception
       when no_data_found then
          --this is the else part, if you do not want to do anything, then
          -- just put null;
   end;
   --subsequent logic
end;

Why this is the proper way:

  • it does not use count(*), you are not trying to get a count of matching records, you are simply testing for existence.
  • it is the fastest way, if col1 is indexed, you will not read any table data, just the index.
  • you avoid a double comparison, once in the select clause, and once in the if statement.

Upvotes: 1

Rami Alshoubaki
Rami Alshoubaki

Reputation: 67

i think it should be the way around.

select count(*) from table1 into var1_cnt
where table1.col1 = var1;

if var1_cnt > 0 then
.......
end if;

Upvotes: 0

Dimitri Harding
Dimitri Harding

Reputation: 141

Depending on which language you are using you can return the select statement result to an array or object after which you can perform the conditional statement on the array or object:

EX. IF var1 in ARR..

Upvotes: 1

Related Questions