Skip to Main Content
  • Questions
  • changing where clause using case statement

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, H.

Asked: January 14, 2020 - 9:22 am UTC

Answered by: Chris Saxon - Last updated: January 17, 2020 - 8:52 am UTC

Category: SQL - Version: 10g

Viewed 1000+ times

You Asked

Hello Tom

Is it possible to change the where condition (using case statement) based on certain variable?

For example
var T varchar2(1)
exec :T := 'E';
var E number;
exec :E := 7788;
var N varchar2(20)
exec :N := 'MILLER';

select empno, ename
from emp
where
-- how to use case statement to vary the condition based on :T
-- if :T = 'E' then the condition should be
where empno = :E
-- and if :T = 'N' then the condition should be 
where ename like :N

and we said...

Wellll, you can kinda do this by having a case expression on either side of your condition.

For example:

var T varchar2(1)
exec :T := 'E';
var E number;
exec :E := 100;
var N varchar2(20)
exec :N := 'Neena';

select employee_id, first_name 
from   hr.employees
where  case
  when :T = 'E' then to_char ( :E )
  when :T = 'N' then :N
end = case
  when :T = 'E' then to_char ( employee_id )
  when :T = 'N' then first_name
end;

EMPLOYEE_ID    FIRST_NAME   
           100 Steven        

exec :T := 'N';

select employee_id, first_name 
from   hr.employees
where  case
  when :T = 'E' then to_char ( :E )
  when :T = 'N' then :N
end = case
  when :T = 'E' then to_char ( employee_id )
  when :T = 'N' then first_name
end;

EMPLOYEE_ID    FIRST_NAME   
           101 Neena   


But this is a TERRIBLE idea!

Using expressions like this destroys the optimizer's ability to use indexes. So these queries will be slow.

It's far better to inspect the value of T first, then run the appropriate (static) SQL statement:

begin
  if :T = 'E' then

    select ...
    into   ...
    from   hr.employees
    where  employee_id = :E; 

  elsif :T = 'N' then

    select ...
    into   ...
    from   hr.employees
    where  first_name = :N;

  end if; 
end;
/

and you rated our response

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Excellent

January 15, 2020 - 7:35 am UTC

Reviewer: H

That was great. You have shown a great way to use case on either side of the operator. In the original question, I used "=" in first case and "like" in second case, is it possible to dynamically change relational operator too?

-- if :T = 'E' then the condition should be
where empno = :E
-- and if :T = 'N' then the condition should be 
where ename like :N

Chris Saxon

Followup  

January 15, 2020 - 12:08 pm UTC

I think you missed the part where I said:

But this is a TERRIBLE idea!

;)

But no, you can't switch operator like this. Remember LIKE with no wildcards is the same as =.

January 15, 2020 - 1:08 pm UTC

Reviewer: A reader


on OR Expansions

January 16, 2020 - 10:14 am UTC

Reviewer: Rajeshwaran, Jeyabal

How about an OR clause like this?

that would be index friendly and also opens up OR Expansion optimization.

demo@PDB1> create table t as
  2  select object_id as empno,
  3     object_name as ename,
  4     created as hiredate,
  5     data_object_id as comm
  6  from all_objects;

Table created.

demo@PDB1> create index t_empno_idx on t(empno);

Index created.

demo@PDB1> create index t_ename_idx on t(ename);

Index created.

demo@PDB1> variable e number
demo@PDB1> variable n varchar2(30)
demo@PDB1> variable t varchar2(1)
demo@PDB1> exec :t :='E'; :e := 55;

PL/SQL procedure successfully completed.

demo@PDB1> set autotrace traceonly explain statistics
demo@PDB1> select *
  2  from t
  3  where ( empno = :e and :t = 'E' )
  4  or ( ename =:n and :t = 'N') ;


Execution Plan
----------------------------------------------------------
Plan hash value: 2111273234

---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |     1 |    50 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T           |     1 |    50 |     4   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |             |       |       |            |          |
|   3 |    BITMAP OR                        |             |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |             |       |       |            |          |
|*  5 |      INDEX RANGE SCAN               | T_ENAME_IDX |       |       |     3   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS   |             |       |       |            |          |
|*  7 |      INDEX RANGE SCAN               | T_EMPNO_IDX |       |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME"=:N AND :T='N' OR :T='E' AND "EMPNO"=TO_NUMBER(:E))
   5 - access("ENAME"=:N)
   7 - access("EMPNO"=TO_NUMBER(:E))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          1  physical reads
          0  redo size
        761  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

demo@PDB1> exec :t :='N'; :n := 'Tom';

PL/SQL procedure successfully completed.

demo@PDB1> select *
  2  from t
  3  where ( empno = :e and :t = 'E' )
  4  or ( ename =:n and :t = 'N') ;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2111273234

---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |     1 |    50 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T           |     1 |    50 |     4   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |             |       |       |            |          |
|   3 |    BITMAP OR                        |             |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |             |       |       |            |          |
|*  5 |      INDEX RANGE SCAN               | T_ENAME_IDX |       |       |     3   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS   |             |       |       |            |          |
|*  7 |      INDEX RANGE SCAN               | T_EMPNO_IDX |       |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME"=:N AND :T='N' OR :T='E' AND "EMPNO"=TO_NUMBER(:E))
   5 - access("ENAME"=:N)
   7 - access("EMPNO"=TO_NUMBER(:E))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        597  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

demo@PDB1>


Based on the value set on the bind variable T - appropriate portion of the plan will execute.
Chris Saxon

Followup  

January 16, 2020 - 2:08 pm UTC

Good suggestion. The optimizer's still likely to fall back to a full table scan if you have more than a couple of columns to choose from though.

January 16, 2020 - 6:16 pm UTC

Reviewer: Chuck Jolley from OKC, OK USA

Why not just union all?
Won't the optimizer simply ignore the clauses where :t isn't appropriate because of them being impossible to make true?

select *
  from t
where empno = :e and :t = 'E' 
union all
select *
 from t
where ename =:n and :t = 'N';

Chris Saxon

Followup  

January 17, 2020 - 8:52 am UTC

Another good suggestion!

It might get fiddly if there are standard predicates to apply every time. Or you need to join the table. But definitely better than "double case" :)

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.