CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10g
Carl
Carl

Reputation: 67

Oracle PL/SQL: Create a short circuit query

I'm somewhat old school, so I call this short circuiting.

But I have 3 tables in Oracle. I also have a "ProcessFlag." If any of the data in the tables matches my criteria, I want to set the procesdFlag = 1.

I can obviously do this with if and else statements.

 select count(*) into myCounter from from Table1 where processflag = 1;
 if myCounter = 0 then
      select count(*) into myCounter from Table2 where processflag = 1;
      if myCounter = 0 then
           select count(*) into myCounter from Table3 where processflag = 1;
           if myCounter = 0 then
                 processFlag = 0;
           else
                 processFlag = 1;
           end if
      else
           processFlag = 1;
      end if
 else
       processFlag = 1;
 end if

So, Assuming I got all my if/eles's correct, I think you see what I'm trying to do. If there is any row, in any table, that has the processFlag = 1, then I want to process.

This is just a small example for demonstration, but my real world has about 10 tables and I really don't want to have to nest if/else statements that deep if I don't need to. And some of these tables have a lot of data, so if something is found in the 1st table, there is no need to check subsequent tables (which is why I called it short circuiting, because as soon as something is found, there is no need to process remaining code).

If there a cleaner/efficient way to do this in Oracle?

Upvotes: 0

Views: 125

Answers (3)

Popeye
Popeye

Reputation: 35910

You can use case when for short circuit query as following::

Select case 
when (select 1 from table1 where processflag = 1 and rownum =1) = 1 -- small table in terms of number of rows
then 1 
when (select 1 from table2 where processflag = 1 and rownum =1) = 1 -- larger than table1 table in terms of number of rows and so on
then 1
..
Else 0 end into processFlag
From dual;

Case when will stop execution of the statement once it finds one match.

Also you can give order of the table in the case when statement for Better performance, use small tables first and large tables at the end in when clause for good performance.

Cheers!!

Upvotes: 1

Miguel Angel Alonso
Miguel Angel Alonso

Reputation: 46

You can use rownum:

select count(*) into myCounter
from (
    select * from Table1 where processflag = 1
    ) l where rownum = 1;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Do you want something like this?

select . . .
from . . .
where exists (select 1 from table1 where processflag = 1) or
      exists (select 1 from table2 where processflag = 1) or
      exists (select 1 from table3 where processflag = 1) ;

That is, you can make your query conditional on a bunch of exists conditions. You could use something similar to actually set a flag, if you are using PL/SQL.

Upvotes: 0

Related Questions